User defined function to count pay days in current month.

[dbmathis]

I need User defined function to count pay days in current month. Let'
see if I can explain this idea right the first time. Big grin

I am in need of a user defined function like the following:

-paydays(year, month, first pay, pay period)

paydays() output = # of paydays in current month based on:
-
year = -current year- format = YYYY
month = -current month- format = MM
first pay = -date of first pay check- format = MM/DD/YYYY
pay period = -days between pay days- format = DD

assumptions are that the person gets paid at least once a month. firs
pay can be any date <= current month and current year. current mont
and current year can be any date >= first pay.

Example:

paydays(2005, 10, "8/29/05", 14)

would spit out a 2
-------------------------

I am creating a cashflow for my personal finances. I need a use
defined function that based on the above args can dynamically determin
how many paydays are in the current month.

I get paid every 7 days and my wife gets paid every 14 days. This mean
I get paid 4 or 5 times a month and that my wife gets paid 2 or 3 time
month. I was thinking in my sleep last night about how wonderful i
would be If I or someone looking for something similiar could jus
dynamically define pay period and first pay day in a function an
automatically get the number of paydays in a current month..

If any of you are up for the challenge of coding this I would greatl
appreciate it. Thank you all for being such a helpful community.

I hope I was clear and very descriptive of what my idea is. I hav
trouble with this sometimes.

thanks

 

[Tom Ogilvy]

Public Function paydays(lyear As Long, _
lmonth As Long, dtfirstpayday, lpayperiod)
Dim dtStart As Date, dtEnd As Date
Dim dtStart1 As Date, dtEnd1 As Date
dtStart1 = dtfirstpayday
dtEnd1 = DateSerial(lyear, lmonth, 0)
dtEnd = DateSerial(lyear, lmonth + 1, 0)
lNumdays = dtEnd1 - dtStart1
lpays = lNumdays \ lpayperiod
dtStart = dtStart1 + lpayperiod * lpays
lNumdays = dtEnd - dtStart
paydays = lNumdays \ lpayperiod
End Function


seems to work.

 

[Bob Phillips]

You can actually do this with a formula, no UDF needed

=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(2005,10,1)-DATE(2005,8,29)&":"&DATE(200
5,11,0)-DATE(2005,8,29))),14)=0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[dbmathis]

Tom,

Actually that UDF is wonderful! I was wondering if you could make i
inclusive to handle a situation like the following?

=paydays2(2005, 8, "8/1/2005", 14)

and produce a 3 including the 1st of August in the results?

If not I can work around that . The UDF works great though! Thank yo
a 1000 billion times

 

[Tom Ogilvy]

Public Function paydays2(lyear As Long, _
lmonth As Long, dtfirstpayday, lpayperiod)
Dim dtStart As Date, dtEnd As Date
Dim dtStart1 As Date, dtEnd1 As Date
dtStart1 = dtfirstpayday
dtEnd1 = DateSerial(lyear, lmonth, 0)
dtEnd = DateSerial(lyear, lmonth + 1, 0)
lNumdays = dtEnd1 - dtStart1
lpays = lNumdays \ lpayperiod
dtStart = dtStart1 + lpayperiod * lpays
lNumdays = dtEnd - dtStart
paydays = lNumdays \ lpayperiod + _
IIf(month(dtStart1) = lmonth And year(dtStart1) = _
lyear, 1, 0)
End Function