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!

 

[dvanallen]

I wish I could just post an attachment of my file instead of recreating it!

Here's what I have so far:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Begin Date[/TD]
[TD]End Date[/TD]
[TD]Hourly Employees
Time Card Deadline[/TD]
[TD]Employee Payroll
Cut-off Date[/TD]
[TD]Pay Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2015[/TD]
[TD]=A2+14[/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B2+1,-1,11,Holidays)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=A2+15[/TD]
[TD]=EOMONTH(A5,0)[/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B3+1,-1,11,Holidays)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=DATE(YEAR(A2),MONTH(A2)+1,MIN(DAY(A2),
DAY(DATE(YEAR(A2),MONTH(A2)+1+1,0))))[/TD]
[TD]=A4+14[/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B4+1,-1,11,Holidays)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=A4+15[/TD]
[TD]=EOMONTH(A5,0)[/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B5+1,-1,11,Holidays)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=DATE(YEAR(A4),MONTH(A4)+1,MIN(DAY(A4),
DAY(DATE(YEAR(A4),MONTH(A4)+1+1,0))))[/TD]
[TD]=A6+14[/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B6+1,-1,11,Holidays)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=A6+15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=WORKDAY.INTL(B7+1,-1,11,Holidays)
Does not work for 4/1/15; Result is 3/31/15 instead[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]12/16/2015[/TD]
[TD]12/31/2015[/TD]
[TD]12/22/2015[/TD]
[TD]12/28/2015[/TD]
[TD]12/31/2015
(1/1/16 is a holiday)[/TD]
[/TR]
</tbody>[/TABLE]

Holiday Range:
[TABLE="width: 264"]
<tbody>[TR]
[TD="align: right"]1/1/2015[/TD]
[TD][/TD]
[TD]New Year's Day[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2015[/TD]
[TD][/TD]
[TD]MLK Day[/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2015[/TD]
[TD][/TD]
[TD]Presidents’ Day[/TD]
[/TR]
[TR]
[TD="align: right"]5/25/2015[/TD]
[TD][/TD]
[TD]Memorial Day[/TD]
[/TR]
[TR]
[TD="align: right"]7/4/2015[/TD]
[TD][/TD]
[TD]Independence Day[/TD]
[/TR]
[TR]
[TD="align: right"]9/7/2015[/TD]
[TD][/TD]
[TD]Labor Day[/TD]
[/TR]
[TR]
[TD="align: right"]10/12/2015[/TD]
[TD][/TD]
[TD]Columbus Day[/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2015[/TD]
[TD][/TD]
[TD]Veterans Day[/TD]
[/TR]
[TR]
[TD="align: right"]11/26/2015[/TD]
[TD][/TD]
[TD]Thanksgiving Day[/TD]
[/TR]
[TR]
[TD="align: right"]12/25/2015[/TD]
[TD][/TD]
[TD]Christmas Day[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2016[/TD]
[TD][/TD]
[TD]New Year's Day[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, can you please re-post the table from the post#11 with the expected values manually entered for all of the columns?

 

[FormR]

Does not work for 4/1/15; Result is 3/31/15 instead

I don't understand why the result should not be 31 March 2015?

Here is a link to the file:
https://www.dropbox.com/s/5xv8ltv3e03zp19/MrExcel-822572.xlsx?dl=0

Note my dates are formatted as YYYY-MM-DD

Excel Workbook
	A	B	C	D	E	F	G
1	Begin Date	End Date	Hourly Employees Time Card Deadline	Employee Payroll Cut-off Date	Pay Date		Holiday Range:
2	2015-01-01	2015-01-15			2015-01-15		2015-01-01
3	2015-01-16	2015-01-31			2015-01-31		2015-01-19
4	2015-02-01	2015-02-15			2015-02-14		2015-02-16
5	2015-02-16	2015-02-28			2015-02-28		2015-05-25
6	2015-03-01	2015-03-15			2015-03-14		2015-07-04
7	2015-03-16	2015-03-31			2015-03-31		2015-09-07
8	2015-04-01	2015-04-15			2015-04-15		2015-10-12
9							2015-11-11
10							2015-11-26
11							2015-12-25
12							2016-01-01
Sheet1

#VALUE!

</td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=60" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Namen verstehen</a></td></tr></table></td></tr></table>

 

[dvanallen]

Yes, please see below. Again, I'm sorry for not providing enough information. I wish I could post attachments!

Please also allow me to clarify that paychecks can't be received on Saturdays, Sundays or holidays. Here is the full calendar for 2015:

[TABLE="width: 549"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Beg Date[/TD]
[TD]End Date[/TD]
[TD]Hourly Employees
Time Card Deadline[/TD]
[TD]US Employees Payroll Cut-off Date[/TD]
[TD]Pay Date[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1/15/2015[/TD]
[TD="align: right"]1/7/2015[/TD]
[TD="align: right"]1/12/2015[/TD]
[TD="align: right"]1/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2015[/TD]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]1/23/2015[/TD]
[TD="align: right"]1/27/2015[/TD]
[TD="align: right"]1/30/2015[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2/15/2015[/TD]
[TD="align: right"]2/6/2015[/TD]
[TD="align: right"]2/10/2015[/TD]
[TD="align: right"]2/13/2015[/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2015[/TD]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]2/21/2015[/TD]
[TD="align: right"]2/24/2015[/TD]
[TD="align: right"]2/27/2015[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]3/15/2015[/TD]
[TD="align: right"]3/6/2015[/TD]
[TD="align: right"]3/10/2015[/TD]
[TD="align: right"]3/13/2015[/TD]
[/TR]
[TR]
[TD="align: right"]3/16/2015[/TD]
[TD="align: right"]3/31/2015[/TD]
[TD="align: right"]3/20/2015[/TD]
[TD="align: right"]3/29/2015[/TD]
[TD="align: right"]4/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]4/15/2015[/TD]
[TD="align: right"]4/7/2015[/TD]
[TD="align: right"]4/10/2015[/TD]
[TD="align: right"]4/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]4/16/2015[/TD]
[TD="align: right"]4/30/2015[/TD]
[TD="align: right"]4/22/2015[/TD]
[TD="align: right"]4/28/2015[/TD]
[TD="align: right"]5/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]5/15/2015[/TD]
[TD="align: right"]5/7/2015[/TD]
[TD="align: right"]5/12/2015[/TD]
[TD="align: right"]5/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]5/16/2015[/TD]
[TD="align: right"]5/31/2015[/TD]
[TD="align: right"]5/22/2015[/TD]
[TD="align: right"]5/27/2015[/TD]
[TD="align: right"]6/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]6/15/2015[/TD]
[TD="align: right"]6/5/2015[/TD]
[TD="align: right"]6/12/2015[/TD]
[TD="align: right"]6/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]6/16/2015[/TD]
[TD="align: right"]6/30/2015[/TD]
[TD="align: right"]6/22/2015[/TD]
[TD="align: right"]6/26/2015[/TD]
[TD="align: right"]7/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]7/15/2015[/TD]
[TD="align: right"]7/7/2015[/TD]
[TD="align: right"]7/10/2015[/TD]
[TD="align: right"]7/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]7/16/2015[/TD]
[TD="align: right"]7/31/2015[/TD]
[TD="align: right"]7/22/2015[/TD]
[TD="align: right"]7/28/2015[/TD]
[TD="align: right"]7/31/2015[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/7/2015[/TD]
[TD="align: right"]8/11/2015[/TD]
[TD="align: right"]8/14/2015[/TD]
[/TR]
[TR]
[TD="align: right"]8/16/2015[/TD]
[TD="align: right"]8/31/2015[/TD]
[TD="align: right"]8/21/2015[/TD]
[TD="align: right"]8/27/2015[/TD]
[TD="align: right"]9/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]9/15/2015[/TD]
[TD="align: right"]9/8/2015[/TD]
[TD="align: right"]9/10/2015[/TD]
[TD="align: right"]9/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]9/16/2015[/TD]
[TD="align: right"]9/30/2015[/TD]
[TD="align: right"]9/22/2015[/TD]
[TD="align: right"]9/28/2015[/TD]
[TD="align: right"]10/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]10/15/2015[/TD]
[TD="align: right"]10/7/2015[/TD]
[TD="align: right"]10/9/2015[/TD]
[TD="align: right"]10/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]10/16/2015[/TD]
[TD="align: right"]10/31/2015[/TD]
[TD="align: right"]10/22/2015[/TD]
[TD="align: right"]10/27/2015[/TD]
[TD="align: right"]10/30/2015[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]11/15/2015[/TD]
[TD="align: right"]11/6/2015[/TD]
[TD="align: right"]11/10/2015[/TD]
[TD="align: right"]11/13/2015[/TD]
[/TR]
[TR]
[TD="align: right"]11/16/2015[/TD]
[TD="align: right"]11/30/2015[/TD]
[TD="align: right"]11/20/2015[/TD]
[TD="align: right"]11/25/2015[/TD]
[TD="align: right"]12/1/2015[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]12/15/2015[/TD]
[TD="align: right"]12/7/2015[/TD]
[TD="align: right"]12/10/2015[/TD]
[TD="align: right"]12/15/2015[/TD]
[/TR]
[TR]
[TD="align: right"]12/16/2015[/TD]
[TD="align: right"]12/31/2015[/TD]
[TD="align: right"]12/22/2015[/TD]
[TD="align: right"]12/28/2015[/TD]
[TD="align: right"]12/31/2015[/TD]
[/TR]
</tbody>[/TABLE]

 

[dvanallen]

My formula rules are as follows:
* All pay period begin dates should be the 1st or 16th of the month, regardless of the day of the week.
* All pay period end dates should be the 15th or last day of the month
* Time card employees have 5 business days from the previous end date to turn in their hours. (For example, if the pay period ends on 1/31/15, time cards are due 2/6.)
* Payroll cut-off dates are 3 business days prior to the pay date excluding Saturdays, Sundays and holidays.
* Pay dates should be either the 15th or 1st. (I've chosen not to pay on the last day of the month because it changes too much!) Pay days cannot fall on Saturdays, Sundays or holidays. For those days, the default pay date should be the previous day.

I'll also add that the formula for the Begin date should be:
* For 1/16/15 and all 16ths) =A4+15
* For 2/1/15 (and all 1sts) =DATE(YEAR(A4),MONTH(A4)+1,DAY(A4))

Where A4 is the 1st of the prior month. In other words, 2 rows down the formula would be repeated with A6 instead.

Does this help? Thank you so much for walking this through with me!

 

[FormR]

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.

Please also allow me to clarify that paychecks can't be received on Saturdays, Sundays or holidays.

This does change things - but can you explain why the "Pay date" for the "End date" of 31st March is the 1st April and not the 31st March?

 

[FormR]

* Pay dates should be either the 15th or 1st. (I've chosen not to pay on the last day of the month because it changes too much!) Pay days cannot fall on Saturdays, Sundays or holidays. For those days, the default pay date should be the previous day.

I think this is new information - It's easier to use the last day of the month - but I think I understand you data now.

 

[FormR]

For the pay day try:

=WORKDAY(B2+IF(DAY(B2)=15,1,2),-1,holidays)

For columns A & B try the formulas I posted in post#13

 

[dvanallen]

YES, YES, YES. Thank you!

I'm reading this formula now and trying to understand it. Is it possible to use what's above (pasted into E2) as a basis for the formula in columns C and D?

For D2, I have: =WORKDAY.INTL(G2,-3,1,HOLIDAYS) and it seems to work

 

[dvanallen]

For C3 I have: =WORKDAY.INTL(B2,5,1,HOLIDAYS) and it seems to work.

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