Calculation for biweekly pay periods in a given month

**cstricklin** · 10-06-2012, 03:53 AM

I'm having difficulty writing/finding a calculation that, given a start date and a month (should I include the current year or what?), calculates the number of biweekly pay periods in that month. For example, if an employee is paid first on October 12, 2012, and I'm looking at the month of November of 2012, I would like the calculation to return the number 2, since they would be paid on the 9th and the 23rd of November.

Would someone be able to assist me?

**dwint** · 10-06-2012, 09:34 AM

let's say,
1st paid date is cell B2 = 12 October 2012
2nd paid date is cell B3
3rd paid date is cel B4

so, formula in B3=B2 +14
copy it down
you'll get 3th paid is 9th Nov 2012 and 4th paid is 23rd Nov 2012

**daddylonglegs** · 10-06-2012, 09:50 AM

Let's say you have the first pay date in A1

Now if you put any 1st of month date in A5 then this formula will give you the number of paydates in that month

=3-(FLOOR(A5-A$1,14)+A$1+42 >EOMONTH(A5,0))

**cstricklin** · 10-06-2012, 10:15 AM

Thank you for replying, but that is not what I'd requested. Perhaps I wasn't very clear. I'll try to explain better what I'm looking to accomplish.

Suppose:

A1 is the date of an employee's first paycheck, let's say Friday, January 4, 2013
A2 through L2 are the targets for the calculation

A2 (January) should contain 2 because January, 2013 would have two paydays
B2 (February) should contain 2 because February, 2013 would have two paydays
C2 (March) should contain 3 because March, 2013 would have three paydays
D2 (April) should contain 2 because April, 2013 would have two paydays
E2 (May) should contain 2 because May, 2013 would have two paydays
F2 (June) should contain 2 because June, 2013 would have two paydays
G2 (July) should contain 2 because July, 2013 would have two paydays
H2 (August) should contain 3 because August, 2013 would have three paydays
I2 (September) should contain 2 because September, 2013 would have two paydays
J2 (October) should contain 2 because October, 2013 would have two paydays
K2 (November) should contain 3 because November, 2013 would have three paydays
L2 (December) should contain 2 because December, 2013 would have two paydays

Maybe there's already a function that does this, or maybe my brain is so tired right now that I can't see a simple solution, but any help would be appreciated.

**cstricklin** · 10-06-2012, 10:19 AM

Thank you daddylonglegs, that's more along the lines of what I am requesting.

It's close, but it doesn't appear quite correct. Using the example date of 1/4/2013 I gave above, August yields 3 correctly but March and November do not.

**daddylonglegs** · 10-06-2012, 10:51 AM

Yes, you're right, small tweak to the formula which also makes it work in earlier excel versions:

Assuming A2:L2 contain the first of the month dates you can use this formula in B2 copied down

=3-(A2-MOD(A2-A$1-1,14)+41 >EOMONTH(A2,0))

I agree that March and August should be 3 for your example but I think all other months (including November) should be 2

**cstricklin** · 10-06-2012, 11:06 AM

Yes, my mistake, you are correct, November would also have 2 paydates given this example. Now I just need to extrapolate this for my ultimate usage.

Thanks very much for the help.

**vipulaggarwal** · 02-10-2020, 05:59 PM

Hey!!
Just curious why did you add 41 in the formula?

**Root_** · 02-11-2020, 04:33 PM

With esteemed daddylonglegs being away, I can give you some explanations.

The logic behind his formula is as follows:

1) Find the latest pay date before the target month: A2-(MOD(A2-A$1-1,14)+1)

2) Add 42 days (3 pay periods) to that date: A2-(MOD(A2-A$1-1,14)+1)+42, reduced to A2-MOD(A2-A$1-1,14)+41

3) Compare the resulting date to the end date of the target month. If the resulting date is within the target month, it has 3 pay dates; otherwise, it has 2 pay dates.

Hope this helps.