Calculating number of days between multiple dates (1 Viewer)

[Vann]

Can any one help me with creating codes that will calculate how many days an employee is getting pay per fiscal year and at what level? My fiscal year starts on July 1st and ends on June 30th the following year.

For example Persno 11527 would get pay for:

fiscal year 2005 (7/1/2004 to 6/30/2005) at
Level 1 from 11/8/2004 to 5/1/2005 plus
Level 2 from 5/2/2005 to 6/30/2005


fiscal year 2006 (7/1/2005 to 6/30/2006) at
Level 2 from 7/1/2005 to 04/26/2006 plus
Level 3 from 4/27/2006 to 6/30/2006


fiscal year 2007 (7/1/2006 to 6/30/2007) at
Level 3 from 7/1/2006 to 4/21/2007 plus
Level 4 from 4/22/2007 to 6/30/2007


fiscal year 2008 (7/1/2007 to 6/30/2008) at
Level 4 from 7/1/2007 to 4/15/2008 plus
Level 5 from 7/16/2008 to 6/30/2008


For records with no enddate the enddate is assumed to be equalled to 12/31/9999.


Here's a sample of my records. Persno, PSGroup, Level, Daily Rate, StartDate, and EndDate.


Persno PSGroup Level Daily Rate StartDate EndDate
11527 B420 1 $138.44 11/8/2004 05/01/2005
11527 B420 2 $145.12 5/2/2005 04/26/2006
11527 B420 3 $152.06 4/27/2006 04/21/2007
11527 B420 4 $159.44 4/22/2007 04/15/2008
11527 B420 5 $167.16 4/16/2008
11518 B466 1 $172.00 10/4/2004 04/01/2005
11518 B466 2 $180.25 4/2/2005 03/27/2006
11518 B466 3 $188.95 3/28/2006 03/22/2007
11518 B466 4 $198.17 3/23/2007 03/16/2008
11518 B466 5 $207.92 3/17/2008
11503 B564 1 $273.98 8/23/2004 02/01/2005
11503 B564 2 $287.42 2/2/2005 01/27/2006
11503 B564 3 $301.65 1/28/2006 01/22/2007
11503 B564 4 $316.50 1/23/2007 01/17/2008
11503 B564 5 $332.13 1/18/2008
11489 R34 $513.46 8/9/2004
11483 R40 $673.08 8/2/2004
11481 R32 $446.15 7/26/2004
11470 R29 $384.42 7/19/2004
11454 U75 1 $275.04 11/1/2004 07/01/2005
11454 U75 2 $293.84 7/2/2005 06/26/2006
11453 B543 1 $247.89 7/6/2004 01/01/2005
11453 B543 2 $260.01 1/2/2005 12/27/2005
11453 B543 3 $272.66 12/28/2005 12/22/2006
11453 B543 4 $286.10 12/23/2006 12/17/2007
11453 B543 5 $300.16 12/18/2007
11451 B543 1 $247.89 6/21/2004 12/01/2004
11451 B543 2 $260.01 12/2/2004 11/26/2005
11451 B543 3 $272.66 11/27/2005 11/21/2006
11451 B543 4 $286.10 11/22/2006 11/16/2007
11451 B543 5 $300.16 11/17/2007
11436 R32 $461.54 6/7/2004
11398 B517 2 $229.80 9/27/2004 09/01/2005
11398 B517 3 $240.95 9/2/2005 08/27/2006
11398 B517 4 $252.72 8/28/2006 08/22/2007
11397 B517 2 $229.80 9/27/2004 09/01/2005
11397 B517 3 $240.95 9/2/2005 08/27/2006
11397 B517 4 $252.72 8/28/2006 08/22/2007
11396 B517 2 $229.80 9/27/2004 09/01/2005
11396 B517 3 $240.95 9/2/2005 08/27/2006
11396 B517 4 $252.72 8/28/2006 08/22/2007


Thank you,

Vann

 

[llkhoutx]

The DateDiff function is what you want.