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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
(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.
 
Upvote 0
PayDate=WORKDAY(Date+1,-1,Holidays)

Direct Debit =WORKDAY(Date-1,+1,Holidays)
 
Upvote 0
Re: Calculate pay day

To answer your question for 2018:

[TABLE="width: 230"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Pay Day[/TD]
[TD]Before/After first of Month[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]3/30/2018[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]6/29/2018[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]8/31/2018[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2018[/TD]
[TD="align: right"]11/30/2018[/TD]
[TD="align: right"]-1[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2018[/TD]
[/TR]
</tbody>[/TABLE]

In column E, below the header, put in the list of us bank holidays (https://www.interstatecapital.com/us-bank-holidays/):
[TABLE="width: 75"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1/1/2018[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2018[/TD]
[/TR]
[TR]
[TD="align: right"]2/19/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5/28/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7/4/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10/8/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]11/22/2018[/TD]
[/TR]
[TR]
[TD="align: right"]12/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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
Back
Top