math with year

[Guest]

I need to calculate years of service. I have the hire date in date format
(ex: 10/04/1998 - in cell C4). I have extracted the hire year in D4 using
=YEAR(C4). So far, so good.
C4=hire date
D4=YEAR(C4)

When I try this formula for years of service: =YEAR(TODAY())-YEAR(C4), I get
00.
When I try this formula: =YEAR(TODAY()-D4), I get 05.

Nevermind - I figured out the answer while posting the question. But in case
others have the same question, here's the formula I used:

=VALUE(YEAR(TODAY()))-VALUE(D4)

It works.

Thanks for all your help!

 

[Bob Phillips]

That suggests that C4 is not a date, but a text cell. With a true date
=YEAR(TODAY()-D4) works fine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Clayman,

Glad you worked it out but you didn't need to extract the year into a
seperate cell to do your calculation, you could have done:-

=YEAR(TODAY())-YEAR(C4)

Mike

 

[Guest]

The form does require hire year in addition. Not my code, but the form
itself. Thanks for the tip, though!

 

[David Biddulph]

Are you sure you need VALUE() ?
Did you try just =YEAR(TODAY())-YEAR(D4) ?
That would, of course, make more sense than your original =YEAR(TODAY()-D4),
which tries to subtract a number of years (D4) from a number of days
(TODAY).

The next reason that you are getting strange answers is that you are getting
muddled between what parameters are straight numbers, and which are dates in
Excel's date format.
TODAY() is 39254 (21st June 2007)
You subtracted YEAR(C4), which presumably gave 2007.
This gives 37247, which is equivalent to 22nd December 2001
YEAR(37247) gives 2001
I think you've then displayed that 2001, not as number or general, but as if
it were a date, and put it in "yy" format. 2001 is Excel's date value for
23rd June 1905, which is why the display shows 05.

 

[Bob Phillips]

Maybe, but there was nothing wrong with =YEAR(TODAY())-YEAR(C4) which he
says that he tried.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Are you sure you need VALUE() ?
Did you try just =YEAR(TODAY())-YEAR(D4) ?
That would, of course, make more sense than your original
=YEAR(TODAY()-D4), which tries to subtract a number of years (D4) from a
number of days (TODAY).

The next reason that you are getting strange answers is that you are
getting muddled between what parameters are straight numbers, and which
are dates in Excel's date format.
TODAY() is 39254 (21st June 2007)
You subtracted YEAR(C4), which presumably gave 2007.
This gives 37247, which is equivalent to 22nd December 2001
YEAR(37247) gives 2001
I think you've then displayed that 2001, not as number or general, but as
if it were a date, and put it in "yy" format. 2001 is Excel's date value
for 23rd June 1905, which is why the display shows 05.