User defined function to count pay days in current month.

I need User defined function to count pay days in current month. Let's 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. first pay can be any date <= current month and current year. current month 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 user defined function that based on the above args can dynamically determine how many paydays are in the current month.

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

If any of you are up for the challenge of coding this I would greatly 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 have trouble with this sometimes.

thanks.

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.

--
Regards,
Tom Ogilvy

"dbmathis" <[email protected]> wrote in
message news:[email protected]...
>
> I need User defined function to count pay days in current month. Let's
> 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. first
> pay can be any date <= current month and current year. current month
> 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 user
> defined function that based on the above args can dynamically determine
> how many paydays are in the current month.
>
> I get paid every 7 days and my wife gets paid every 14 days. This means
> I get paid 4 or 5 times a month and that my wife gets paid 2 or 3 time a
> month. I was thinking in my sleep last night about how wonderful it
> would be If I or someone looking for something similiar could just
> dynamically define pay period and first pay day in a function and
> automatically get the number of paydays in a current month..
>
> If any of you are up for the challenge of coding this I would greatly
> 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 have
> trouble with this sometimes.
>
> thanks.
>
>
> --
> dbmathis
> ------------------------------------------------------------------------
> dbmathis's Profile:
http://www.excelforum.com/member.php...o&userid=26735
> View this thread: http://www.excelforum.com/showthread...hreadid=399918
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> I need User defined function to count pay days in current month. Let's
> 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. first
> pay can be any date <= current month and current year. current month
> 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 user
> defined function that based on the above args can dynamically determine
> how many paydays are in the current month.
>
> I get paid every 7 days and my wife gets paid every 14 days. This means
> I get paid 4 or 5 times a month and that my wife gets paid 2 or 3 time a
> month. I was thinking in my sleep last night about how wonderful it
> would be If I or someone looking for something similiar could just
> dynamically define pay period and first pay day in a function and
> automatically get the number of paydays in a current month..
>
> If any of you are up for the challenge of coding this I would greatly
> 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 have
> trouble with this sometimes.
>
> thanks.
>
>
> --
> dbmathis
> ------------------------------------------------------------------------
> dbmathis's Profile:
http://www.excelforum.com/member.php...o&userid=26735
> View this thread: http://www.excelforum.com/showthread...hreadid=399918
>

Tom,

Actually that UDF is wonderful! I was wondering if you could make it 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 you a 1000 billion times!

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

--
Regards,
Tom Ogilvy


"dbmathis" <[email protected]> wrote in
message news:[email protected]...
>
> Tom,
>
> Actually that UDF is wonderful! I was wondering if you could make it
> 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 you
> a 1000 billion times!
>
>
> --
> dbmathis
> ------------------------------------------------------------------------
> dbmathis's Profile:
http://www.excelforum.com/member.php...o&userid=26735
> View this thread: http://www.excelforum.com/showthread...hreadid=399918
>