Please help me set paydays formula

[pennynikkel]

I'm trying to create formulas to set paydays into a spreadsheet. For example

1) First pay day is 10 calendar days after the previous month's end. i.e. Feb 16 - 29 would be pay day March 10, but consider that if the 10th day falls on a Saturday, Sunday or Holiday, then it has to be the next soonest working day. ie. June 16 - 30 would be pay day July 9th.

2) 2nd pay day is 10 calendar days after the 15th of the month, with same guidelines as above, in that if the 10th day is a holiday or weekend, then the payday falls on the nearest workday. i.e. September 16 - 30th pay day is October 8th.

Thank you very much.

 

[Zack Barresse]

have a look at the NETWORKDAYS function.

HTH

edit: i believe you have to have the analysis took pak add-in for this.

 

[pennynikkel]

Thank you, and I've looked at NETWORKDAYS...does this not give me the number of days between two dates? I am looking for the return value to be the actual date.

 

[kskinne]

the following formula will test to see if the date ten days after the previous month-end lands on a saturday or sunday, and if it does, adds the correct number of days to account for that.

=(EOMONTH(NOW(),-1)+10)+CHOOSE(WEEKDAY((EOMONTH(NOW(),-1)+10),1),1,0,0,0,0,0,2)

it however does not account for holidays, but i am sure there is someone on this board who can modify this or come up with a better formula...in the meantime you can mess w/ it and see if you can make it work for you, and by changing the EOMONTH() function to the DATE() function, you can also modify it to find the pay day 10 workdays after the 15th as well


hth
kevin

 

[Zack Barresse]

don't know exactly what your looking for, but does this help??...

WTR04.xls
	Q	R	S	T
35		1stpayday	2ndpayday
36	1/15/2004	12/18/2003	1/29/2004
37	2/16/2004	1/19/2004	3/1/2004
38	3/15/2004	2/16/2004	3/29/2004
39	4/15/2004	3/18/2004	4/29/2004
40	5/14/2004	4/19/2004	5/28/2004
41	6/15/2004	5/18/2004	6/29/2004
42	7/15/2004	6/17/2004	7/29/2004
43	8/16/2004	7/19/2004	8/30/2004
44	9/15/2004	8/18/2004	9/29/2004
45	10/15/2004	9/17/2004	10/29/2004
46	11/15/2004	10/18/2004	11/29/2004
47	12/15/2004	11/17/2004	12/29/2004
Feb04

Q36 formula is:
=IF(WEEKDAY(DATE(2004,1,15))=1,DATE(2004,1,15)+1,IF(WEEKDAY(DATE(2004,1,15))=7,DATE(2004,1,15)-1,DATE(2004,1,15)))
...finding the closest workday to the fifteenth (not counting holidays)

R36 formula is:
=WORKDAY(DATE(2004,MONTH(Q36),1),-10)
...which can be copied down.

S36 formula is:
=WORKDAY(Q36,10)
...which can be copied down.

pros:
R36 & S36 can be added to for holiday compensation (nothing currently, as each employer is different)

cons: Q36 does not take holidays into account. probably a shorter way

hope this helps.