#1
|
|||
|
|||
Converting serial number to years, months and days
I have a serial number representing a person's age say 12,345 days in cell B1. what is the formula to convert (or display) that in years months and days?
|
#2
|
|||
|
|||
Have you ever formatted a cell? Just format the cell as a date...
Or maybe you yant to extract the year, day, or month from that date: use Year, Day, or Month functions... |
#3
|
||||
|
||||
Although you can format the number as a date, be aware that the results will only be approximate, due to the underlying assumptions Excel makes about leap years and the date you're counting from.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Formatting the "age" cell B1 to date does not give the correct answer, if the date of birth is for instance 1 jan 1012, the date today is set to 1 jan 2013 the substarcted value displayed in date format is 31 days, 12 months and 00 years, which is incorrect.
I found a function in the internet called DATEDIF which interestigly is an "undocumented" function but that does nothing different or new compared to simple substraction of the cells containing DOB and TODAY.. both give the number of days between two dates. I found an another Integer formula that works, just seems awfully complicated for a simple calculation, I thought Excel would have something simple to solve that common question. |
#5
|
||||
|
||||
The solution Catalin posted works with the problem you described in your first post (nothing more than a count of days), within the limitations I indicated in my first post.
Your second post relates to an entirely different scenario. You can't expect to get the 'right' answer if you don't provide all the relevant detail. As for DATEDIF, your assertion that it "does nothing different or new compared to simple substraction of the cells" shows you really haven't taken the time to learn how in works - it does far more than a simple addition/subtraction can do. See: http://www.cpearson.com/excel/datedif.aspx Given the additional information you've now provided, which shows that you're actually trying to calculate an age based on the difference between two dates (in A1 & B1), try: =DATEDIF(A1,B1,"Y")&" years, "&DATEDIF(A1,B1,"YM")&" months & "&DATEDIF(A1,B1,"MD")&" days"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Thank you macropod, my second email is still searching for a simple answer to the problem of displaying the age of a person in years, months and days, when the DOB and todays date are known. It is interesting how volatile written communication may be, so easy to have misunderstandings if the presenter, in this case myself, assumes that the listener understands my writing.
I must have done something wrong testing the DATEDIF formula first time, it works now. many Thanks |
#7
|
||||
|
||||
Quote:
=DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months & "&DATEDIF(A1,TODAY(),"MD")&" days" Similarly, if you had only a number of days in B1, without knowing the DOB you could get an approximation with: =DATEDIF(0,B1,"Y")&" years, "&DATEDIF(0,B1,"YM")&" months & "&DATEDIF(0,B1,"MD")&" days"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I get auto-updated "number of days since specified date" on MS word? | terry795 | Word | 16 | 01-07-2016 04:33 PM |
Increase serial number for every print | atomtm | Word | 1 | 06-15-2012 05:39 AM |
Serial Number Issue | rehan5001 | Excel | 1 | 06-08-2011 02:46 AM |
Setting up recurring months to skip some months etc. | dwelch@ykfireprevention.c | Outlook | 0 | 11-30-2010 10:15 AM |
Help needed using the serial number date with sumifs - whole office is stumped | FraserKitchell | Excel | 3 | 01-06-2010 12:24 PM |