Calculate remaining pay periods based on today's date

hkelly · Aug 11, 2008

Hello all.

I need a formula that will calculate the number of remaining pay periods based on today. I have 26 pay periods in a year (bi-weekly).

Any suggestions?

Thanks for any help.

Heather

Oaktree · Aug 11, 2008

Welcome to the board.

Try:

=26-INT((WEEKNUM(TODAY())+1)/2)

or similar.

Note that WEEKNUM requires the analysis toolpak.

hkelly · Aug 11, 2008

Outstanding! Thank you very much.

Heather

dr427 · Mar 17, 2009

I used the search function and found this! It is almost what I need as well, but I need to force the date from "today" to 7/1/09 as the reference point against their hire date. That way I can determine how many pay periods are at rate #1 and how many pay periods are at rate #2 given they get an annual increase on their review date.

Employee "A" may have a review date of 4/1/09 giving them 20 pay periods of rate $X.XX and 6 pay periods of rate $X.XX before 7/1/10.

I tried =DATEDIF("7/1/09",K71,"m") with review date being the K71.

7/1/09 is our fiscal year start.

Any suggestions?

dr427 · Mar 17, 2009

What am I doing wrong? Row 2 for instance shows 2 months. I need it to say X number of pay periods remaining until 7/1/2010. I have 5 pay periods for July & August that should be pay rate 1 then 21 pay periods at pay rate 2.

Book3

A

B

C

D

E

F

1

ReviewDate

PP#1

#1Rate

PP#2

#2Rate

09-10AnnualSalary

2

9/1/2009

2

12.33

24

18.36

52,113.60

3

1/1/2010

6

15.69

20

16.56

47,638.08

4

10/1/2009

3

14.20

23

15.55

44,828.00

5

3/1/2009

#NUM!

11.79

#NUM!

17.25

#NUM!

6

7/1/2009

0

8.66

26

15.69

45,689.28

7

12/1/2010

17

24.58

9

27.56

74,580.80

8

5/1/2009

14

17.89

12

19.65

54,461.12

Sheet1

dr427 · Mar 17, 2009

I think I am getting closer!

If I take the formula above and replace it with:

=26-INT((WEEKNUM(("7/1/2009"))+1)/2)+14 This formula will equal 26 (which for me means 26 pay periods)

What do I need to do to determine the pay period difference between that result of 26 and the next review date such as 4/1/09? I know the result is 6. There are 6 pay periods between 4/1/09 and 7/1/09, but I don't know how to add that date into the above formula. Or really the cell that contains the date to give me the difference expressed in pay periods remaining.

dr427 · Mar 24, 2009

An ideas??? I am still struggling.

barry houdini · Mar 24, 2009

Hello dr427,

When exactly are the pay periods, you didn't say?

Shouldn't all the dates in A2:A8 be between 1st July 2009 and 30th June 2010?

Perhaps it would be simple to just list all the pay dates. I put the first pay date in J2 and then used this formula in J3

=J2+14

to get the next pay date.....and then copied this down to J27 to get all pay dates in the year. For next year you can just change the date in J2 to get all the dates....

Then in B2 you can use this formula copied down

=COUNTIF(J$2:J$27,"<"&A2)

and similar in D2

=COUNTIF(J$2:J$27,">="&A2)

Then in F2

=(B2*C2+D2*E2)*112

see below

Book1