Day of the Year in Microsoft Excel

jpluimers

Jan 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