Please help me set paydays formula

pennynikkel

New Member
Joined
Sep 3, 2003
Messages
41
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
have a look at the NETWORKDAYS function.

HTH

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

pennynikkel

New Member
Joined
Sep 3, 2003
Messages
41
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
don't know exactly what your looking for, but does this help??...

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,895
Messages
5,766,978
Members
425,392
Latest member
Booknerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top