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.