15th & EOM pay schedule

[SeattleAnnie]

I have scanned related posts and still do not find an answer for our need. New payroll system and I need to oversee my team for paydays, reporting time, etc.

1) Pay dates are 15th and EOM (Sun/Hol pay proceeding Friday).
2) Administrator needs exceptions/hours 4 work days prior to pay date.
3) Pay period is 1-15, and 16-EOM (so some hours/exceptions are reported ahead and may need adjustment.
(I'm not seeing how to paste my actual format here - so it doesn't represent that it sees the "Pay Period" as text, not number.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Pay Date[/TD]
[TD]Report House By[/TD]
[TD]Pay Period[/TD]
[/TR]
[TR]
[TD]1/15/2016[/TD]
[TD]1/11/2016[/TD]
[TD]1/1/2016 - 1/15/2016[/TD]
[/TR]
</tbody>[/TABLE]

Previously I've had a simple form with these three fields that adjusts for holidays and such - yet that was an "every other Friday" which is simple. Help....please?

THANKS!

 

[Dave Patton]

Excel 2010
	C	D
1	Pay Date	Report House By
2	Fri Jan 15, 2016	Mon Jan 11, 2016
3	Sun Jan 31, 2016	Tue Jan 26, 2016
2a

Cell Formulas
Range	Formula
D2	=WORKDAY(C2,-4)
D3	=WORKDAY(C3,-4)

Workday also has a parameter for Holidays.

 

[SeattleAnnie]

Thank you Dave! Is there a way to fill the Pay Date row based on this "workday" formula? I have tried to work with a leapfrog option (15th/EOM) and am getting nowhere. I really appreciate your assistance!

 

[Dave Patton]

first thought in C3

=IF(DAY(C2)=15,EOMONTH(C2,0),DATE(YEAR(C2),MONTH(C2)+1,15)) copy down

 

[SeattleAnnie]

I'm glad to know I was somewhat on the right track. Yet still not quite there. It tells me "too few arguments when (trying to account weekends anf holidays) I adjusted your formula to "WORKDAY" from "DAY" =IF(WORKDAY(C2)=15,EOMONTH(C2,0),DATE(YEAR(C2),MONTH(C2)+1,15)). Getting closer though!

 

[SeattleAnnie]

I'm finding this:

[h=2]Syntax[/h]WORKDAY(start_date, days, [holidays])
The WORKDAY function syntax has the following arguments:

• Start_date Required. A date that represents the start date.
  
• Days Required. The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
  
• Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

 

[Dave Patton]

Please note that I put the formula in the Pay Date column.

Do you have a question about the information in post #5?

The date formulas are not text.

The Holiday parameter is a range of holiday dates; the range can be named say "Holidays".

 

[SeattleAnnie]

I'm obviuosly not making clear what I thought was a clear question. I will regroup and try and state my needs differently tomorrow. Thank you for your time.

 

[Dave Patton]

Excel 2010
	C	D	E
1	Pay Date	Report House By	Pay Period
2	Fri Jan 15, 2016	Mon Jan 11, 2016	01-01-2016 - 01-15-2016
3	Sun Jan 31, 2016	Tue Jan 26, 2016	01-16-2016 - 01-31-2016
4	Mon Feb 15, 2016	Tue Feb 09, 2016	02-01-2016 - 02-15-2016
5	Mon Feb 29, 2016	Tue Feb 23, 2016	02-16-2016 - 02-29-2016
6	Tue Mar 15, 2016	Wed Mar 09, 2016	03-01-2016 - 03-15-2016
7	Thu Mar 31, 2016	Fri Mar 25, 2016	03-16-2016 - 03-31-2016
2aa

Cell Formulas
Range	Formula
C2	=Start+14
C3	=IF(DAY(C2)=15,EOMONTH(C2,0),DATE(YEAR(C2),MONTH(C2)+1,15))
D2	=WORKDAY(C2,-4)
D3	=WORKDAY(C3,-4)
E2	=TEXT(42370,"mm-dd-yyyy")&" - "&TEXT(C2,"mm-dd-yyyy")
E3	=TEXT(C2+1,"mm-dd-yyyy")&" - "&TEXT(C3,"mm-dd-yyyy")

Named Ranges
Name	Refers To	Cells
rT	=zList!$B$2:$D$11
Start	='2aa'!$F$1

 

[SeattleAnnie]

Thank you.