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!

 

[FormR]

YES, YES, YES. Thank you!

Phew! Glad we got there

Is it possible to use what's above (pasted into E2) as a basis for the formula in columns C and D?

Yes, I would use:
C: =WORKDAY(E2,-5,holidays)
D: =WORKDAY(E2,-3,holidays)

But these do produce different results than your example data - you'll have to explain the differences if the example data is correct and the formula is wrong.

 

[FormR]

Just curious . . .why did you use WORKDAY vs. INTL?

The whole point of using .INTL was because in your first post you indicated that Saturdays were working days. WORKDAY() assumes Sat and Sun are weekend days.

 

[TheGreatCaesar]

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!

=IF(DAY(TodaysDate)<=15,WORKDAY(EOMONTH(TodaysDate,-1)+16,-1,Holidays[Date]),WORKDAY(EOMONTH(TodaysDate,0)+1,-1,Holidays[Date]))