How to calculate the number of days until a deadline in Excel
Formula (Excel and Google Sheets)
=B2-$F$1Subtract today's date from the deadline: =B2-$F$1, with today's date in F1. Past deadlines come out negative.
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Excel 2013 and later
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
Subtracting today from the deadline gives the days still to go, and a negative result tells you how many days late it is. F1 holds the date to count from; put =TODAY() there to make the sheet live, or type a date to see the plan as of that day.
=B2-$F$1Result18
B211/16/2026The value in B2.
$F$110/29/2026The value in F1.
B2-$F$118The - operator subtracts.
In Google Sheets
The same formula works in Google Sheets: =B2-$F$1. We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(0,B2-$F$1) | 18, 33, 0…Different result | Shows 0 instead of a negative number once the deadline has passed. Past deadlines show 0. |
| =DAYS(B2,$F$1) | 18, 33, 0… | The same result with DAYS. Note the order: deadline first. |
| =NETWORKDAYS($F$1,B2)-1 | 12, 23, 0…Different result | Working days left, not counting today. Counts Monday to Friday only. |
Common mistakes
- With
B2-TODAY()in every row the numbers change each day and can't be checked later. A single "today" cell is easier to audit and to test. - If the result shows as a date, format the column as a number.
- A deadline with a time, like 5:00 PM, gives a fraction. Use
INT(B2)in the subtraction.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the number of days until a deadline
C2, calculate how many days are left until each deadline, counting from the date in F1. Fill down to C7.Questions
How do I show "Overdue" instead of a negative number?
=IF(B2<$F$1,"Overdue",B2-$F$1&" days left").
How do I count only business days left?
=NETWORKDAYS($F$1,B2,holidays)-1 skips weekends and holidays and leaves out today.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.