Day Number of the Year – Formula for Excel or Google Sheets
In this Article
This tutorial will teach you how to calculate a date’s day number of the year or find the nth day of the year. Examples work for Excel and Google Sheets.
Date’s Day Number of the Year
To calculate a date’s day number of the year you can calculate the difference between the date and the first day of the year:
|
1 |
=B3-DATE(YEAR(B3),1,1)+1 |
Notice we need to add 1 to the end of the formula to calculate the correct day number. Instead we can set the day to 0 in the Date Function:
|
1 |
=B3-DATE(YEAR(B3),1,0) |
nth Day of the Year
Conversely you can calculate the nth day of the year by adding n days to 12/31 of the previous year:
|
1 |
=DATE(2020,1,0)+C3 |
Notice we do this by once again setting day =0 in the Date Function.
Today’s Day Number
Use the TODAY Function to calculate today’s day number:
|
1 |
= TODAY() - DATE(YEAR(TODAY()),1,0) |
Google Sheets – Day Number Of The Year
All of the above examples work exactly the same in Google Sheets as in Excel.
Excel Practice Worksheet
Practice Excel functions and formulas with our 100% free practice worksheets!
- Automatically Graded Exercises
- Learn Excel, Inside Excel!