# How to get the day of the year from a date in Excel

Subtract the last day of the previous year: `=A2-DATE(YEAR(A2),1,0)` returns 1 to 366.

**Excel:** `=A2-DATE(YEAR(A2),1,0)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date | Day of year |
| 2026-01-01 |  |
| 2026-02-28 |  |
| 2026-03-01 |  |
| 2028-03-01 |  |
| 2026-12-31 |  |

Formula in B2, filled to B6: `=A2-DATE(YEAR(A2),1,0)`

Result: 1; 59; 60; 61; 365

## How it works

`DATE(YEAR(A2),1,0)` is day 0 of January, which is December 31 of the year before. Subtracting it from the date leaves the day number: 60 for March 1 in a common year, 61 in a leap year.

## Other ways

- `=A2-DATE(YEAR(A2),1,1)+1`: The same, counting from January 1.
- `=TEXT(A2-DATE(YEAR(A2),1,0),"000")`: A three-digit text code like 060, used in Julian dates. (Three-digit text.)

## Common mistakes

- This is the day of the year, sometimes called the Julian day, but not the astronomical Julian Day Number.
- Format the result as a number, or it inherits the date format.

## Questions

**How do I convert a day number back to a date?**

`=DATE(2026,1,B2)`: DATE rolls day 60 into March 1.

**How do I make a YYDDD Julian date?**

`=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/day-of-the-year