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]

Welcome to the forum!

Not sure I fully understand, but if you have XL2010 or higher you could try:

=WORKDAY.INTL(B27+1,-1,11,holidays)

EDIT: This decrements the date in cell B27 to the previous working day (working days being Mon to Sat) if that date is a Sunday or a holiday date. Hopefully that is what you wanted!?

 

[dvanallen]

That's interesting. I'm not sure I understand how it works. If I use the same formula with a start date of 12/1/17 (i.e., cell B27 is 11/30/17, and the start date is B27+1), it gives me 11/30/17. It should give me 12/1/17 because December 1st is a non-holiday and a Friday.

Why did it result in 11/30/17 instead of 12/1/17?

 

[FormR]

EDIT:. Maybe I'm not understanding what you are trying to do. I assumed the date in B27 was date that you were trying to adjust to the previous working day. Are you actually trying to find the next payday from that date?

 

[dvanallen]

It is not in my holiday list. Holidays is an optional array of dates. If I remove that parameter from the formula, it still doesn't return the dates I need.

=Workday(43070,-1,11) returns 11/30/17. I need a formula that would return 12/1/17.
=Workday(43070,0,11) returns 12/1/17. That is good.
=Workday(43101,-1,11) returns 12/30/17. That is good.
=Workday(43101,0,11) returns 1/1/18. I need a formula that would return 12/30/17.

Hmm . . .

 

[FormR]

Sorry, I edited my post. I'm not entirely sure what you are trying to do - can you try explaining again in simple terms?

i.e. I have a date in cell B27 and I want to return ....?..... based on the fact that working are Mon to Sat.

 

[dvanallen]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Begin Date
[/TD]
[TD]End Date
[/TD]
[TD]Time Cards Due
[/TD]
[TD]Payroll Submission Cutoff
[/TD]
[TD]Pay Date
[/TD]
[/TR]
[TR]
[TD]1st of the month
[/TD]
[TD]=A1+14
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15th
[/TD]
[/TR]
[TR]
[TD]=A1+15
[/TD]
[TD]=EOMONTH(A3,0)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1st
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15th
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1st
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5 business days after previous period's end (business days can't be Sundays or holidays). So C3 would be 5 biz days after B2
[/TD]
[TD]3 business days before pay date (business days can't be Sundays or holidays)
[/TD]
[TD]must not be a Sunday or holiday
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

I might be being a bit dense here.. but where does cell B27 fit into this? and for which column(s) are you trying to calculate a value?

 

[dvanallen]

Sorry, it looks like I forgot that my header row is row 1. References to A1 should point to the cell with the description, "1st of the month."

For my purposes, B27 is the end of the pay period -- either the 15th or the last day of the month. The formula we're working on should appear in column E.

Once we figure that out, columns C and D are just slight variations of the formula in column E. I can probably figure those out if we can crack column E. Thanks!

 

[FormR]

Are you sure the current formulas in column A and B are right?

Anyway I've taken a stab at all the columns:

The formula in A2 is stand alone; the formulas in B2:E2 are copied down as far as needed. The formula in A3 is copied down as far as needed.

Excel Workbook
	A	B	C	D	E
1	Begin Date	End Date	Time Cards Due	Payroll Submission Cutoff	Pay Date
2	01 Feb 2014	15 Mar 2014	21 Mar 2014	12 Mar 2014	15 Mar 2014
3	16 Mar 2014	31 Mar 2014	05 Apr 2014	27 Mar 2014	31 Mar 2014
4	01 Apr 2014	15 May 2014	21 May 2014	12 May 2014	15 May 2014
5	16 May 2014	31 May 2014	06 Jun 2014	28 May 2014	31 May 2014
6	01 Jun 2014	15 Jul 2014	21 Jul 2014	11 Jul 2014	15 Jul 2014
Sheet2

Excel Workbook
	B
27	31 Jan 2014
Sheet2

If we are still along way off, can you re-post the table from post#7 but manually completed with the expected output for all the columns (including what the value of B27 is)?