# Dates Formula for payroll

#### LeLapinLeStrange

##### New Member
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>

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi,

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

Code:
``````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

Excel 2010
DEFG
4Year20162016
5
6Month33
7
87-Jan-163-Mar-16Mar 03, 16
921-Jan-1617-Mar-16Mar 17, 16
100-Jan-0031-Mar-16Mar 31, 16
11
1d
Cell Formulas
RangeFormula
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.

Last edited:
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.

Code:
``=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,"θ")``

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.

You are welcome.

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

Replies
3
Views
780
Replies
2
Views
564
Replies
10
Views
517
Replies
24
Views
699
Replies
17
Views
773

1,196,078
Messages
6,013,291
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back