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 ofA1=YEAR(A1)-1is year beforeA1=DATE(YEAR(A1)-1,12,13)is the last day of year beforeA1=A1-DATE(YEAR(A1)-1,12,13)is the day of the year ofA1
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 ofA1=DATE(YEAR(A1),1,1)is the first day of the year ofA1=DATE(YEAR(A1),1,1)-1is the last day of the year beforeA1=A1-(DATE(YEAR(A1),1,1)-1)is the day of the year ofA1=A1-DATE(YEAR(A1),1,1)+1is a simplification of the day of the the year ofA1
[Wayback/Archive] excel days from start of year – Google Search
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a Reply