How to Calculate Day of the Year in Google Sheets


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:

day of year for today in Google Sheets

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:

day of year for date in cell in Google Sheets

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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *