Calculation using a date

[KKH]

I have worksheets where I have a formula to calculate the number of years and
months of service. I would like to use that date to calculate excess annual
leave hours. (Example: calculation shows at 23 years 4 months, I need to use
this to calculate the following: 0 to 4 years =360; 5 to 9 years=624; 10-19
years=640; 20+ years=680) Note: The calculation for years and months will be
different on each employee. Can anyone help?

 

[Mike H]

Hi,

You don't give us the formula for how you got 23 years 4 months but I assume
it's a mixture of formula and text concatenated so try this

=IF(LEFT(A1,FIND(" ",A1)-1)+0>=20,680,IF(LEFT(A1,FIND("
",A1)-1)+0>=10,640,IF(LEFT(A1,FIND(" ",A1)-1)+0>=5,624,360)))

Mike

 

[T. Valko]

It looks like all you need to know is the years.

Create a 2 column table like this with the lower year boundary for each
interval in the left column:

......F.....G
1...0.....360
2...5.....624
3..10....640
4..20....680

Then, if:

A1 = 23 years 4 months

=IF(A1="","",LOOKUP(--LEFT(A1,FIND(" ",A1)-1),F1:G4))

 

[KKH]

Sorry, I used =DATEDIF(P1,TODAY(),"y")&" years "&DATEDIF(P1,TODAY(),"ym")&"
months". Will this make a difference on how the other function works?

 

[Mike H]

Nope

Sorry, I used =DATEDIF(P1,TODAY(),"y")&" years "&DATEDIF(P1,TODAY(),"ym")&"
months". Will this make a difference on how the other function works?

 

[Rick Rothstein]

You might want to reconsider using the DATEDIF function. It is an
undocumented (and, thus, probably and unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this message as part of a newsgroup question...

**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward.

 

[KKH]

This worked beautifully. Thank you very much.

Hi,

You don't give us the formula for how you got 23 years 4 months but I assume
it's a mixture of formula and text concatenated so try this

=IF(LEFT(A1,FIND(" ",A1)-1)+0>=20,680,IF(LEFT(A1,FIND("
",A1)-1)+0>=10,640,IF(LEFT(A1,FIND(" ",A1)-1)+0>=5,624,360)))

Mike