# How to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?

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

-
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

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

-
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