Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
how to calculate $1000/year at 5% for 25 years showing each year | Setting up and Configuration of Excel | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel |