Creating a Semi-Monthly Payroll Calendar

[dvanallen]

I'm trying to create a semi-monthly payroll calendar that takes into account holidays and Sundays. Payroll has to be submitted to the processor X days before the pay date. If the pay date (or any day between it and the payroll submission date) is a holiday or Sunday, it can't be included in the calculation for X days.

The columns I my spreadsheet are:
A|B|C|D
Begin Date|End Date|Cut-off Date|Pay Date

Here's my current formula, which is placed in column D, the "Pay Date" column.

=IF(AND(WEEKDAY(B27+1,2)<7,ISNA(MATCH(B27+1,Holidays,0)))=FALSE,WORKDAY(B27,-1,'Holidays'!$V$4:$V$13),"false")

• Column B is the Ending Date column. B27 is the ending date of the previous payroll period: Sunday, December 31, 2017
• Holidays is a built-in Excel holiday library
• 'Holidays'!$V$4:$V$13 is a named range containing my own list of holiday dates

Pay dates are on the 1st and 15th of the month. The formula should recognize that 1st is a holiday (1/1/18) and revise the pay date to be one non-holiday, business day before the 1st. That would be December 30th.

As the formula is written above, it returns the date of 12/29/17. That's one day too early.
If I change the -1 at the end of the formula to 0, then the formula changes to 12/31 (which is a Sunday). Paychecks can't be received on Sundays.

Please help!

[pjwhitfield]

Isn't Sunday a 0 rather than 7?

Your code seems to say if the weekday < 7, shouldnt that be > 0?

[ELeGault]

Sunday is 0 so that will impact your formula...


If I was going to tackle this I would start by laying out a calendar of up to 60 days from today.

Then I would list out the Holidays that my company considers Non Pay date Holidays.

I set this one up real quick and I am sure it does not meet all criteria, but it is a start and we can work out what other logic you need.

Basically I created 2 sheets, one with a running 60 days based on todays date.
Above the Date is the following formula

Please Login or Register  to view this content.

This is checking to see if we have listed any dates as a holiday, if it does than bring over the name of the holiday.
If not be blank

Then there is the following code below the date
[CODE=IF(OR(E$2<>"",WEEKDAY(E$4)=1),"",IF(OR(DAY(E$4)=1,AND(E$4=EOMONTH(E$4,0),F$5=""),AND(E$4=(EOMONTH(E$4,0)-1),F$5="",G$5=""),DAY(E$4)=15,AND(DAY(E$4)=14,F$5=""),AND(DAY(E$4)=13,F$5="",G$5="")),"PAY Date",""))[/CODE]
First we check to see 2 things, Is there a name in the holiday field in row 1 or is the current day Sunday (1)
If either of those are found to be true than it is not a paying day no matter what so stay blank
Then we check the following conditions, keep in mind if the day was Sunday or a holiday the following would not be considered
Is the current day the First
Is the current day the end of the month and the next day pay date check (which is the first) is it blank (If it is blank, this means the next day fell on a Sunday or holiday so lets pay today)
Is it the day before the last day of the month and the following 2 days blank. (Both being blank would mean a non paying holiday and a Sunday or second holiday fell in line, so lets pay 2 days early)
Is the current day the 15th
is the current day the 14th and the following day pay date check blank
Is the current day the 13th and the following 2 days blank
If those any of those are true then be Pay date otherwise remain blank.

Take a peek and see if this is what you are looking for.

HolidayPayDate.xlsx