date conversion

gronkette1 · Mar 27th, 2002, 04:32 PM

This is probably very basic, but:

I need to do the following:

today's date minus birthdate = years of service....

thanks

Yogi Anand · Mar 27th, 2002, 04:38 PM

Quote:

On 2002-03-27 11:32, gronkette1 wrote:
This is probably very basic, but:

I need to do the following:

today's date minus birthdate = years of service....

thanks

Hi gronkette1:
If birthdate is 3/1/81 and is entered in cell A1, then

=year(today())-year(A1)

will give you the age in years -- it may not necessarily give you the years of service ... Ha Ha!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 15:00 ]

Dave Patton · Mar 27th, 2002, 04:53 PM

To calculate the difference in years,
with Earliest Date in A1 and Latest date in B1, use =Datedif(A1,B1,"y") You could use today() for latest date.

Note. Datedif is in most versions of Excel but it was only documented in Excel 2000.

You can expand Datedif to show months, and days (Some anomalies with days). The following also includes text "Years" etc.

=DATEDIF(A40,A38,"y")&" years, "&DATEDIF(A40,A38,"ym")&" months"

[ This Message was edited by: Dave Patton on 2002-03-27 12:53 ]

Yogi Anand · Mar 27th, 2002, 06:01 PM

Quote:

Note. Datedif is in most versions of Excel but it was only documented in Excel 2000.

Thanks Dave:
For resolving this mystery for me -- I know I could work with DATEDIF in Excel 97, but I couldn't find in the FUNCTION gallery!

BabyTiger · Mar 27th, 2002, 06:11 PM

Hi Dave,

I have tried to find that DATEDIF function in Excel 97, but no luck.

I guess my Excel version is not one of your "most versions" then.

Thanks

Yogi Anand · Mar 27th, 2002, 06:40 PM

Hi BabyTiger:
Have you tried using the function in Excel 97 -- as I mentioned in my post, I was familiar that the function existed and I could use the function in Excel 97, but I could not find it in the function gallery, nor could I find help on this function within Excel 97. So, give it a shot and use the function -- you may be pleasantly surprised that it does exist -- it is just not documented!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 04:32 PM	#1
gronkette1 Join Date: Mar 2002 Location: Jackson, MI Posts: 88	This is probably very basic, but: I need to do the following: today's date minus birthdate = years of service.... thanks

Mar 27th, 2002, 04:53 PM	#3
Dave Patton Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 1,761	To calculate the difference in years, with Earliest Date in A1 and Latest date in B1, use =Datedif(A1,B1,"y") You could use today() for latest date. Note. Datedif is in most versions of Excel but it was only documented in Excel 2000. You can expand Datedif to show months, and days (Some anomalies with days). The following also includes text "Years" etc. =DATEDIF(A40,A38,"y")&" years, "&DATEDIF(A40,A38,"ym")&" months" [ This Message was edited by: Dave Patton on 2002-03-27 12:53 ]

Mar 27th, 2002, 06:11 PM	#5
BabyTiger Join Date: Mar 2002 Location: Wellington Posts: 104	Hi Dave, I have tried to find that DATEDIF function in Excel 97, but no luck. I guess my Excel version is not one of your "most versions" then. Thanks

Mar 27th, 2002, 06:40 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,448	Hi BabyTiger: Have you tried using the function in Excel 97 -- as I mentioned in my post, I was familiar that the function existed and I could use the function in Excel 97, but I could not find it in the function gallery, nor could I find help on this function within Excel 97. So, give it a shot and use the function -- you may be pleasantly surprised that it does exist -- it is just not documented!