You can use the following formulas to calculate the day of the year in Google Sheets:
Formula 1: Calculate Day of the Year for Today
=TODAY()-DATE(YEAR(TODAY()),1,0)
This formula will return the day of the year for the current day.
Formula 2: Calculate Day of the Year for Date in Cell
=A2-DATE(YEAR(A2),1,1)+1
This formula will return the day of the year for the date in cell A2.
Formula 3: Calculate Date for Day of the Year
=DATE(2022,1,0)+A2
This formula will return the date that corresponds to the day of the year in cell A2.
The following examples show how to use each formula in practice.
Example 1: Calculate Day of the Year for Today
This article was written on 11/2/2022. To find the day of the year for today, we can type the following formula into Google Sheets:
=TODAY()-DATE(YEAR(TODAY()),1,0)
The following screenshot shows how to use this formula in practice:
We can see that 11/2/2022 is the 306th day of the year.
Example 2: Calculate Day of the Year for Date in Cell
To find the day of the year for a date in cell A2, we can type the following formula into Google Sheets:
=A2-DATE(YEAR(A2),1,1)+1
The following screenshot shows how to use this formula in practice:
We can see that 3/31/2022 is the 90th day of the year.
Example 3: Calculate Date for Day of the Year
To find the date that corresponds to the nth day of the year in cell A2, we can type the following formula into Google Sheets:
=DATE(2022,1,0)+A2
The following screenshot shows how to use this formula in practice:
We can see that the 90th day of the year in 2022 is 3/31/2022.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Add & Subtract Years from Date in Google Sheets
How to Sort by Date in Google Sheets
How to AutoFill Dates in Google Sheets