 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006how to calculate the age of an employee
hi, I have a list of employees with their date of births - for example 1/1/1988. I need to have a column that works out the age with todays date. Is this possible? Thanks Suzie See Chip Pearson's website for instructions on using the DATEDIF function to calculate age: http://www.cpearson.com/excel/datedif.htm Does that help? *********** Regards, Ron XL2002, WinXP > hi, > [quoted text clipped - 7 lines] > > Suzie perfect thanks so much suzie > See Chip Pearson's website for instructions on using the DATEDIF function to > calculate age: [quoted text clipped - 18 lines] > > > > Suzie If name is in A2, Put birthdate in B2 C2 = b2-TODAY() Problem is that this will give you an age in Years , ie 43.21 If you want it in Years, Months, Days there is a function DATEDIF So add the following, this will Years, Months and Days in D, E and F D2 = DATEDIF($B2,TODAY(),"y") E2 = DATEDIF($B2,TODAY(),"ym") F2 = DATEDIF($B2,TODAY(),"md") Steve > hi, > [quoted text clipped - 8 lines] > > Suzie Hi Suzie, Another way is with your birthdate in A1, then in B1 put =TODAY()-A1 And Format B1 as custom yy"y " mm"m " dd"d" HTH Martin First try i got a date as the result But hey, the formatting made sense of the numbers Neat > Hi Suzie, > [quoted text clipped - 6 lines] > HTH > Martin Hi Martin That's very neat!!  SignatureRegards Roger Govier > Hi Suzie, > [quoted text clipped - 6 lines] > HTH > Martin Unfortunately not as neat as I thought! I just tried it on a question in another group which was comparing the dates 14/02/1980 to 28/02/1985 which should return 5y 0m 14d but this method returns 5y 1m 14d Think it needs a bit more investigation. Regards Martin Hi Martin I had just been carrying out the same task myself, and realised the "flaw". I also tried in with dates of 16/09/06 and 19/09/06 and it gives the "appearance" of 0y 01m 03d so it is adding an extra month to the result. I will also continue to "play" as it looks such a neat way of doing things. SignatureRegards Roger Govier > Unfortunately not as neat as I thought! > [quoted text clipped - 6 lines] > Regards > Martin Hi Roger, Changing the formula to =(TODAY()-A1)-31 does seem to get it fairly close, I don't think it would ever be more than a day out although it does give some strange results like 4y 12m 1d. I'm thinking it has something to do with the known bug that exists with the 1900 date system that I have read about on these groups before but can't quite bring to mind right now. Sounds like a question for Biff to me. Regards Martin OK Made a bit more sense out of it. It's no bug just normal maths. Usual counting starts at 0, but when you are using month formatting it starts at 1. 33 when shown in date format refers to 02/02/1900 so in yy mm dd format will show as 00y 02m 02d yet in actual time it is really 00y 01m 02d. As I said before the minus 31 in the formula should be close enough for most practical purposes, so long as your not betting on the result ;-) Regards Martin if you change your format to yyyy"y..." it shows the full 19.. format for the year In raw form today() is 38979 (19/09/2006) 1/1/1950 is 18264 Today()-date = 20715 (17/09/1956) But actually it's 56y 9m and 18days thnk it's hitting leap days in the years it is using My brain is going need a cup of tea before I post any more Steve > Unfortunately not as neat as I thought! > [quoted text clipped - 6 lines] > Regards > Martin Hi Steve No it isn't 9 months and 18 days, as we haven't passed the end of September yet. It seems to be out by a whole month each time. Like you, a cup of tea is required - and come to think of it, I haven't eaten any lunch yet!!! Be back later. SignatureRegards Roger Govier if you change your format to yyyy"y..." it shows the full 19.. format for the year In raw form today() is 38979 (19/09/2006) 1/1/1950 is 18264 Today()-date = 20715 (17/09/1956) But actually it's 56y 9m and 18days thnk it's hitting leap days in the years it is using My brain is going need a cup of tea before I post any more Steve On Tue, 19 Sep 2006 13:40:49 +0100, MartinW <mtmw@hotmail.invalid> wrote: > Unfortunately not as neat as I thought! > [quoted text clipped - 6 lines] > Regards > Martin Here's the basic issue with simply subtracting the dates..... When formatted as any kind of date, Excel interprets the difference as a date serial number and displays the value of that date. Example: Using A1: 02/01/1957 and A2: 02/01/2006 (which is obviously 49 years) 02/01/2006 - 02/01/1957 = 17,897 days Excel interprets that as date serial number for 12/30/1948 Using the custom format of yy"y " mm"m " dd"d", you get the YEAR of that date: 1948 the MONTH of that date: 12 and the DAY of that date: 30 However, using DATEDIF and the fomula from Chip Pearson's site: =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days" That difference calculates to: 49 years, 0 months, 0 days *********** Regards, Ron XL2002, WinXP > Hi Suzie, > [quoted text clipped - 6 lines] > HTH > Martin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.