# Calculate Pay Day

#### DavidEC

##### New Member
Calculate pay day

I get paid on the first of every month. When this falls on a Saturday or Sunday I get paid on the previous Friday. But when this falls on a Bank holiday I get paid on the previous working day. How do I calculate my pay day?

#### DavidEC

##### New Member
(Previously posted on incorrect forum - sorry!)
I get paid on the first of every month. When this falls on a Saturday or Sunday I get paid on the previous Friday and I can calculate that easily But when this falls on a Bank holiday I get paid on the previous working day.
How do I calculate my pay day? In 2018 the 1st April is a Sunday. Normally I would get paid on Friday 30th March. But in 2018 that is a bank holiday (Good Friday) and so I have got paid today the last workday before 1st April where 1st April is not a workday.
In reverse - when a direct debit is due on a weekend or a bank holiday, I can calculate the next working day using =workday.

#### SpillerBD

##### Well-known Member
PayDate=WORKDAY(Date+1,-1,Holidays)

Direct Debit =WORKDAY(Date-1,+1,Holidays)

#### gmhumphr

##### New Member
Re: Calculate pay day

 Month Pay Day Before/After first of Month 1/1/2018 1/2/2018 1 2/1/2018 2/1/2018 0 3/1/2018 3/1/2018 0 4/1/2018 3/30/2018 -2 5/1/2018 5/1/2018 0 6/1/2018 6/1/2018 0 7/1/2018 6/29/2018 -2 8/1/2018 8/1/2018 0 9/1/2018 8/31/2018 -1 10/1/2018 10/1/2018 0 11/1/2018 11/1/2018 0 12/1/2018 11/30/2018 -1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

To solve on your own, put the following into excel:

Headers (A1, B1, C1, D1, E1): Month, Pay Day, Variation, (blank), Holidays

In column A, below the header, put in the first day of every month:
 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018

<colgroup><col></colgroup><tbody>
</tbody>

In column E, below the header, put in the list of us bank holidays (https://www.interstatecapital.com/us-bank-holidays/):
 1/1/2018 1/15/2018 2/19/2018 5/28/2018 7/4/2018 9/3/2018 10/8/2018 11/12/2018 11/22/2018 12/25/2018

<colgroup><col></colgroup><tbody>
</tbody>

In cell B2, put the following formula in (make sure to press CTRL + SHIFT + Enter when committing the formula):
{=IF(OR(A2=\$E\$2:\$E\$11),IF(WEEKDAY(A2+1,2)>=6,A2+7-WEEKDAY(A2),A2+1),IF(WEEKDAY(A2,2)>=6,A2-(WEEKDAY(A2,2)-5),A2))}

The formula checks the first of the month to see if it is a bank holiday (which only fall on weekdays). If it is, it assumes payment on the following day (but just in case, it checks if the next day is a weekend, and keeps going until it finds a business day). If it is not a holiday, it checks to see if it is a weekend, and adjusts backwards until it finds a Friday.

In cell C2, put the following formula in (just as a check to see if it is going to be different that just the first day of the month, based on number of days offset):
=B2-A2

Copy the formulas in B2 and C2 down to the end of the data.

#### DavidEC

##### New Member
PayDate=WORKDAY(Date+1,-1,Holidays)

Direct Debit =WORKDAY(Date-1,+1,Holidays)
Thank you SpillerBD

1,082,505
Messages
5,365,965
Members
400,864
Latest member
RobynP51