math with year
 

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.

math with year
 

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)

"Clayman" wrote in message
...
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!
--
Adios,
Clay Harryman

math with year
 

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


"Clayman" wrote:

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!
--
Adios,
Clay Harryman

math with year
 

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


"Mike H" wrote:

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


"Clayman" wrote:

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!
--
Adios,
Clay Harryman

math with year
 

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.
--
David Biddulph

"Clayman" wrote in message
...
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!
--
Adios,
Clay Harryman

math with year
 

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)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Clayman" wrote in message
...
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!
--
Adios,
Clay Harryman