Calculate Pay Day

DavidEC

New Member
Joined
Mar 29, 2018
Messages
3
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
Joined
Mar 29, 2018
Messages
3
(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:confused:? 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 :cool: 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.
 

gmhumphr

New Member
Joined
Mar 26, 2018
Messages
45
Re: Calculate pay day

To answer your question for 2018:

MonthPay DayBefore/After first of Month
1/1/20181/2/20181
2/1/20182/1/20180
3/1/20183/1/20180
4/1/20183/30/2018-2
5/1/20185/1/20180
6/1/20186/1/20180
7/1/20186/29/2018-2
8/1/20188/1/20180
9/1/20188/31/2018-1
10/1/201810/1/20180
11/1/201811/1/20180
12/1/201811/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.
 

Forum statistics

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

Some videos you may like

This Week's Hot Topics

Top