Table of Contents
Converting a date to the decimal year in Excel is a simple process that can be done in a few steps. First, open the spreadsheet containing the date and select the cell containing the date. Go to the Data tab and select the Text to Columns option in the Data Tools section. Choose Delimited as the file type and click Next. Select the Date option, choose the date format, and click Finish. The date will then be converted to a decimal year in the same cell. This process can also be done with the DATEVALUE function.
You can use the following basic syntax to convert a date to a decimal year in Excel:
=YEARFRAC(1,A2)+1900
This particular formula converts the date in cell A2 to a decimal year.
For example, if the date in cell A2 is 2/5/2023 then this formula will convert it to 2023.094.
This formula works by using the YEARFRAC function, which calculates the fraction of a year represented by the number of whole days between a start and end date.
The value 1 in YEARFRAC represents January 1, 1900.
Thus, YEARFRAC(1, A2) calculates the number of fractional years since January 1, 1900 and then we add 1900 to convert this value to the current year.
The following example shows how to use this formula in practice.
Example: Convert Date to Decimal Year in Excel
Suppose we have the following column of dates in Excel:
We can type the following formula into cell B2 to convert the cell in A2 to a decimal year:
=YEARFRAC(1,A2)+1900
We can then click and drag this formula down to each remaining cell in column B:
Each value in column B represents the decimal year for the corresponding cell in column A.
For example:
- 1/1/2023 as a decimal year is 2023.
- 2/5/2023 as a decimal year is 2023.094.
- 6/15/2023 as a decimal year is 2023.45556.
And so on.
Note: You can find the complete documentation for the Excel YEARFRAC function .