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`

[Wayback/Archive] excel days from start of year – Google Search

–jeroen

### Like this:

Like Loading...