How to Calculate Day of the Year in Google Sheets

To calculate the day of the year in Google Sheets, you can use a combination of the DATE and YEAR functions along with simple subtraction. Follow these steps:

1. Open Google Sheets and create a new sheet or open an existing one.
2. Click on an empty cell where you want to display the day of the year.
3. Type the formula =A1-DATE(YEAR(A1),1,1)+1 and press Enter. Replace A1 with the cell containing the date you want to calculate the day of the year for.

In this formula:

• YEAR(A1) extracts the year from the date in cell A1.
• DATE(YEAR(A1),1,1) creates a date representing the first day of the year.
• A1-DATE(YEAR(A1),1,1) calculates the difference between the date in A1 and the first day of the year.
• +1 is added to the difference to include the first day of the year in the count.

Example

Let's say you have a date in cell A1, and you want to calculate the day of the year in cell B1.

1. Enter the date in cell A1. For example, 2021-09-28.
2. Click on cell B1 and type the formula =A1-DATE(YEAR(A1),1,1)+1.
3. Press Enter. Cell B1 will now display the day of the year for the date in cell A1. In this example, it would show 271.