# How to calculate the days left in the year in Excel

Subtract the date from December 31: `=DATE(YEAR(A2),12,31)-A2`.

**Excel:** `=DATE(YEAR(A2),12,31)-A2` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date | Days left |
| 2026-01-01 |  |
| 2026-10-29 |  |
| 2026-12-31 |  |
| 2028-02-29 |  |
| 2026-07-02 |  |

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

Result: 364; 63; 0; 306; 182

## How it works

`DATE(YEAR(A2),12,31)` is December 31 of the same year, and subtracting the date gives the days still to come, not counting the date itself. The leap day is included automatically: from February 29, 2028 there are 306 days left.

## Other ways

- `=EOMONTH(A2,12-MONTH(A2))-A2`: The same, using EOMONTH to reach December 31.

## Common mistakes

- Add 1 if today should count as a day left.
- Format the result as a number, or it shows as a date in 1900.

## Questions

**How do I get the day number of the year?**

`=A2-DATE(YEAR(A2),1,0)`.

**How do I show the percentage of the year left?**

`=(DATE(YEAR(A2),12,31)-A2)/(DATE(YEAR(A2),12,31)-DATE(YEAR(A2),1,0))`.

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/days-left-in-the-year