#### pennynikkel

##### New Member
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
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
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
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
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.

Replies
8
Views
176
Replies
7
Views
2K
Replies
1
Views
570
Replies
6
Views
325
Replies
3
Views
271

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.

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.

### Which adblocker are you using?

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

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