Home  |  About  | Last |  Submit  |  Contact
AllQuests.com



Previous Question:  macro page breaks printing  Excel QuestionsNext Question:  Scrollable Frame Control  Excel Questions
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
=

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
=

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

Previous Question:  macro page breaks printing  MrExcel Message Board  Excel QuestionsNext Question:  Scrollable Frame Control  MrExcel Message Board  Excel Questions

- 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