Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've found a fair number of formulas online which let me convert birthdate into decimal age in years but I can't find something that converts, for instance, 1.0 into 04-Nov-2011.

Any help would be much appreciated.

EDIT:

Here is an example: I currently have data which has been derived from converting dates of birth (i.e. 04/08/07) to "decimal years" (i.e. 3.75). The original DOB data has since been deleted. I would like to recalculate the DOB from the remaining decimal number which represents years (i.e. What is the date of birth of someone who is 3.75 years old?)

share|improve this question
    
What exactly are you trying to do? Best would be if you give us some examples. A hint: "Dates" in Excel are days since 01-Jan-1900, you can get the number of days between two dates by subtracting them. –  Jens Erat Nov 4 '11 at 11:26
add comment

1 Answer

If you want a rough estimation:

= NOW() - A1 * 365.22

where A1 stores the age.

Explanation:

Excel stores dates using 1900 date system, that is 1 represents 1900/1/1, 2 represents 1900/1/2 and so on.

NOW() retrieves the current date/time. When subtracting age * 365.22 days from it, the result is approximately the date of birth. (Be reminded to format the cell as date)

Note: The formula would be more complex if you want a precise calculation (leap year handling).

share|improve this answer
    
Instead of using current date/time, you can also specify a date by replacing NOW() by DATE(2010,12,31) –  wilson Nov 7 '11 at 11:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.