0

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?)

1
  • 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, 2011 at 11:26

1 Answer 1

1

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).

1
  • Instead of using current date/time, you can also specify a date by replacing NOW() by DATE(2010,12,31)
    – wilson
    Nov 7, 2011 at 11:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .