Home  |  About  | Last |  Submit  |  Contact

 Tweet

 Question Calculate remaining pay periods based on todays date ( MrExcel Message Board Excel Questions )Updated: 2009-04-03 11:45:09 (8)
 Calculate remaining pay periods based on todays date 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
 Answers: Calculate remaining pay periods based on todays date ( MrExcel Message Board Excel Questions )
 Calculate remaining pay periods based on todays date Welcome to the board. Try: =26-INT((WEEKNUM(TODAY())+1)/2) or similar. Note that WEEKNUM requires the analysis toolpak. Oaktree
 Calculate remaining pay periods based on todays date Outstanding! Thank you very much. Heather hkelly
 Calculate remaining pay periods based on todays date 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
Calculate remaining pay periods based on todays date

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.

 Microsoft Excel - Book3 ___Running: xl2002 XP : OS = Windows XP
 File Edit View Insert Options Tools Data Window Help About
 B2D2F2B3D3F3B4D4F4B5D5F5B6D6F6B7D7F7B8D8F8 =

A
B
C
D
E
F
1
Review DatePP #1 #1 Rate PP #2 #2 Rate 09-10 Annual Salary
2
9/1/20092     12.33 24     18.36      52,113.60
3
1/1/20106     15.69 20     16.56      47,638.08
4
10/1/20093     14.20 23     15.55      44,828.00
5
3/1/2009#NUM!     11.79 #NUM!     17.25 #NUM!
6
7/1/20090       8.66 26     15.69      45,689.28
7
12/1/201017     24.58 9     27.56      74,580.80
8
5/1/200914     17.89 12     19.65      54,461.12
 Sheet1

[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

dr427

 Calculate remaining pay periods based on todays date 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
 Calculate remaining pay periods based on todays date An ideas??? I am still struggling. dr427
Calculate remaining pay periods based on todays date

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

 Microsoft Excel - Book1 ___Running: 11.0 : OS =
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B2D2F2B3D3F3J3B4D4F4J4B5D5F5J5B6D6F6J6B7D7F7J7B8D8F8J8J9J10J11J12J13J14J15J16 =

A
B
C
D
E
F
G
H
I
J
K
1
Review DatePP #1#1 RatePP #2#2 RateSalary  PeriodDate
2
01-Sep-20095\$12.3321\$18.36\$50,087.52  103-Jul-2009
3
01-Jan-201013\$15.6913\$16.56\$46,956.00  217-Jul-2009
4
01-Oct-20097\$14.2019\$15.55\$44,223.20  331-Jul-2009
5
01-Mar-201018\$11.798\$17.25\$39,224.64  414-Aug-2009
6
01-Jul-20090\$8.6626\$15.69\$45,689.28  528-Aug-2009
7
01-Dec-200911\$24.5815\$27.56\$76,583.36  611-Sep-2009
8
01-May-201022\$17.894\$19.65\$52,884.16  725-Sep-2009
9
809-Oct-2009
10
923-Oct-2009
11
1006-Nov-2009
12
1120-Nov-2009
13
1204-Dec-2009
14
1318-Dec-2009
15
1401-Jan-2010
16
1515-Jan-2010
 Sheet2

[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

barry houdini

 Calculate remaining pay periods based on todays date Thank you so much! I will play around with your suggestion and let you know if it worked! dr427
 - Source: Calculate remaining pay periods based on todays date MrExcel Message Board Excel Questions- Previous Question: macro page breaks printing MrExcel Message Board Excel Questions- Next Question: Scrollable Frame Control MrExcel Message Board Excel Questions