Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function to count pay days in current month.
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 -- dbmathi ----------------------------------------------------------------------- dbmathis's Profile: http://www.excelforum.com/member.php...fo&userid=2673 View this thread: http://www.excelforum.com/showthread.php?threadid=39991 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function to count pay days in current month.
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function to count pay days in current month.
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function to count pay days in current month.
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 -- dbmathi ----------------------------------------------------------------------- dbmathis's Profile: http://www.excelforum.com/member.php...fo&userid=2673 View this thread: http://www.excelforum.com/showthread.php?threadid=39991 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function to count pay days in current month.
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the number of even weeks in the current month. | Excel Worksheet Functions | |||
how do i display the total number of days in the current month in. | Excel Discussion (Misc queries) | |||
Restoring the current result of failing user-defined functions | Excel Programming | |||
current row of user-defined function? | Excel Programming | |||
current cell in user-defined function | Excel Programming |