Dates Formula for payroll

[LeLapinLeStrange]

Hello,
I have been looking through the threads and I am unable to find a formula that fits the following scenario:

I want to be able to put a year in F4 and a month (1-12) in F6 and payroll dates to populate automatically in fields F8, F9, and F10. If there are only 2 dates in the month, I would like F10 to populate with a "θ" or something equivalent. I have been trying to calcualte it by starting with the first payroll date of 2016 which was January 7th, 2016. I have played around with numbers and equations and am kind of going around in circles. I have looked on other forums and none really fit what I'm trying to accomplish. Attached is a sample of what I am attempting. Any and all direction would be great! Thanks.

Pay Period

Year

F4

Month

F6

Pay Dates

F8

F9

F10

<tbody>
</tbody>

 

[djbomaha]

Hi,

Here is a macro that does what you are describing. I think a VBA macro is the most efficient way to do it.

For i = 42376 To 73415 Step 14
   If (Month(i) = Range("F6") And Year(i) = Range("F4")) Then
      Range("F8") = i
      Range("F9") = i + 14
      If (Month(i + 28) = Range("F6") And Year(i) = Range("F4")) Then
         Range("F10") = i + 28
      Else
         Range("F10") = "NA"
      End If
      Exit Sub
   End If
Next i

Hope that provides some assistance,

Dan

 

[Dave Patton]

Excel 2010
	D	E	F	G
4	Year	2016	2016
5
6	Month	3	3
7
8		7-Jan-16	3-Mar-16	Mar 03, 16
9		21-Jan-16	17-Mar-16	Mar 17, 16
10		0-Jan-00	31-Mar-16	Mar 31, 16
11
1d

Cell Formulas
Range	Formula
E9	=E8+14
E10	=IF(E9+14>EOMONTH(E8,0),0,E9+14)
F9	=F8+14
F10	=IF(F9+14>EOMONTH(F8,0),0,F9+14)
F8	{=MIN(IF(YEAR(N4:N29)=F4,IF(MONTH(N4:N29)=F6,N4:N29)))}
G9	=G8+14
G10	=IF(G9+14>EOMONTH(G8,0),TEXT(0,"0"),TEXT((G9+14),"mmm dd, yy"))
G8	{=MIN(IF(YEAR(N4:N29)=F4,IF(MONTH(N4:N29)=F6,N4:N29)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The range N4:N29 has the bi-weekly payroll dates for the year.

 

[Tetra201]

Give a try to the following formula -- put in in cell F8 and copy down to F9, F10.
Thursday holidays are not handled in this formula, but they can be.

=IF(MONTH(42376+(ROWS($F$8:$F8)-1)*14+INT((DATE($F$4,$F$6,1)-42370)/14)*14)=$F$6,42376+(ROWS($F$8:$F8)-1)*14+INT((DATE($F$4,$F$6,1)-42370)/14)*14,"θ")

 

[LeLapinLeStrange]

Thank you so much! It worked perfectly. I really need to learn more about the IF function on Excel. That was what I was messing up on.

 

[Tetra201]

You are welcome.

BTW, I am curious about payroll for year 2020 -- it has 27 pay periods.