# The Wiert Corner – irregular stream of stuff

• ## Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 4,226 other subscribers

## Day of the Year in Microsoft Excel

Posted by jpluimers on 2023/01/25

Given cell `A1` is a valid date, I wanted to know the day of that date in that year.

My solution is =A1-DATE(YEAR(A1)-1,12,13)

I disliked the solution in [Wayback/Archive] Day of the Year in Microsoft Excel and [Archive] Day of the Year in Excel (In Easy Steps) (excluded from the WayBack machine), as it is unclear where the `+` comes from in their solution `=A1-DATE(YEAR(A1),1,1)+1`

So, here goes my solution, with explanation:

• `=YEAR(A1)` is the year of `A1`
• `=YEAR(A1)-1` is year before `A1`
• `=DATE(YEAR(A1)-1,12,13)` is the last day of year before `A1`
• `=A1-DATE(YEAR(A1)-1,12,13)` is the day of the year of `A1`

The last step works because subtracting two dates in Excel returns the number of days between those two dates (in a similar way, you can add a number to a date to get a new date number days in the future; similarly you can add time portions as fractions of a day).

The linked solution uses:

• `=YEAR(A1)` is the year of `A1`
• `=DATE(YEAR(A1),1,1)` is the first day of the year of `A1`
• `=DATE(YEAR(A1),1,1)-1` is the last day of the year before `A1`
• `=A1-(DATE(YEAR(A1),1,1)-1)` is the day of the year of `A1`
• `=A1-DATE(YEAR(A1),1,1)+1` is a simplification of the day of the the year of `A1`

–jeroen

This site uses Akismet to reduce spam. Learn how your comment data is processed.