How to get the day number of the year (1-365)
✓ Verified in LibreOffice 25.8.7.3Turn a date into its ordinal day of the year — day 1 to 365/366 — for seasonality, day-count keys, or julian-style dates.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =A2-DATE(YEAR(A2),1,1)+1 | Date minus Jan 1 of its own year, plus 1. Format the result cell as a Number. |
| Google Sheets | =A2-DATE(YEAR(A2),1,1)+1 | Identical. |
| LibreOffice Calc | =A2-DATE(YEAR(A2),1,1)+1 | Identical. |
How it works
Subtracting January 1 from the date gives days elapsed since the start of the year, and the +1 makes January 1 itself day 1: March 1, 2026 is day 60 (31 in January + 28 in February + 1). It handles leap years automatically because it's pure date arithmetic — day 60 in a leap year is Feb 29. The reverse (day number back to a date) is =DATE(year,1,day_number). If the result shows as a date, switch the cell format to Number.
Verified, not just documented
We ran =DATE(2026,3,1)-DATE(2026,1,1)+1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 60 — exactly the expected result. Every formula here is confirmed by actually executing it.