Calculation using a date
 

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?

Calculation using a date
 

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

"KKH" wrote:

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?

Calculation using a date
 

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))

--
Biff
Microsoft Excel MVP


"KKH" wrote in message
...
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?

Calculation using a date
 

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" wrote:

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

"KKH" wrote:

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?

Calculation using a date
 

Nope

"KKH" wrote:

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" wrote:

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

"KKH" wrote:

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?

Calculation using a date
 

This worked beautifully. Thank you very much.

"Mike H" wrote:

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

"KKH" wrote:

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?