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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Convert Date to Day of Week
Excel: How to Add & Subtract Weeks from Dates
Excel: Calculate the Number of Weeks Between Dates
