Excel Formula for Days Left Till Date

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for days left until a date is =DATE(2025,12,31)-TODAY(), which returns the number of days between today and the target date. For a cell reference, use =B2-TODAY() where B2 contains the future date.

Understanding Date Arithmetic in Excel

Excel stores dates as serial numbers, where each day is one whole number. January 1, 1900 is serial 1, and today’s date is a large number like 45,000. Subtracting one date from another simply subtracts the serial numbers, giving the difference in days. Because of this, you can use plain subtraction for date differences without any special function. The TODAY() function returns the current date as a serial number, updating every time the worksheet recalculates. This makes =B2-TODAY() dynamic—it changes each day.

Basic Formula with a Cell Reference

Assume your target date is in cell B2. In cell C2, enter:

=B2-TODAY()

If B2 contains 2025-12-31 and today is 2025-01-15, the result is 350. The formula returns a positive number if the date is in the future, zero if it’s today, and a negative number if the date has passed. To avoid negative numbers (for example, if you want to show 0 for past dates), wrap it with MAX:

=MAX(0, B2-TODAY())

This returns 0 for any past or today’s date, and the positive difference otherwise.

Handling Past Dates and Blank Cells

If the target cell is blank, Excel treats it as 0 (which is January 0, 1900), so =B2-TODAY() would return a huge negative number. To show a blank or a message when no date is entered, use an IF check:

=IF(B2="", "", MAX(0, B2-TODAY()))

For past dates, you might want to display “Expired” instead of a negative number:

=IF(B2="", "", IF(B2

This returns "Expired" if the date is before today, otherwise the positive day count.

Using DATEDIF for More Control

While subtraction works for days, the DATEDIF function gives you flexibility for other units. To get days left, use:

=DATEDIF(TODAY(), B2, "d")

This counts full days between today and B2. Note that DATEDIF returns an error if the start date (today) is after the end date (B2), so it’s best used when you know the date is always in the future. For a robust formula that handles past dates, stick with subtraction and MAX.

Working with Deadlines and Conditional Formatting

Days-left formulas are often used in project trackers or expiry alerts. To highlight dates that are within 30 days, select the range with dates, go to Home > Conditional Formatting > New Rule, choose "Use a formula to determine which cells to format", and enter:

=AND(B2<>"", B2-TODAY()<=30, B2-TODAY()>=0)

Set a fill color and click OK. This highlights dates that are between today and 30 days from now. For expired dates, use a similar rule with =B2.

Common Errors and How to Fix Them

  • #VALUE! – Occurs if the cell contains text instead of a date. Ensure the cell is formatted as Date or enter a valid date.
  • Negative numbers – Expected when the date is past; use MAX(0, ...) to clamp to zero.
  • Wrong result due to time – If your dates include times, the subtraction may return a decimal. Use =INT(B2-TODAY()) to round down to whole days.
  • Formula not updating – TODAY() recalculates when the workbook opens or when a change occurs. Press F9 to force recalculation.

Alternatives: NETWORKDAYS and EDATE

If you need to count only working days (Mon–Fri), use NETWORKDAYS:

=NETWORKDAYS(TODAY(), B2)

This returns the number of workdays between today and B2, excluding weekends. To exclude holidays, add a range of holiday dates: =NETWORKDAYS(TODAY(), B2, $H$2:$H$10). For adding months to a date, use EDATE: =EDATE(TODAY(), 3) gives the date three months from today. You can then subtract today from that result to get days left in a quarter.

Frequently Asked Questions

How do I calculate days left in Excel including today?

Subtraction like =B2-TODAY() counts the number of full days between today and the target, not including today. If you want to include today as day 1, add 1: =B2-TODAY()+1. For example, if today is Jan 1 and the target is Jan 2, the formula returns 1, but with +1 it returns 2.

Why does my formula show a date instead of a number?

This happens when the cell is formatted as a date. Select the cell, go to Home > Number Format dropdown, and choose "General" or "Number". The result will then display as a plain integer.

Can I use this formula for a countdown timer?

Yes, but it updates only when the workbook recalculates. To see it change every second, you would need VBA or a circular reference with a volatile function. For most purposes, daily recalculation is enough—just save or open the file to refresh.

How do I show days left as "30 days" or "Expired"?

Use a custom number format: right-click the cell, choose Format Cells, then Custom, and enter 0" days". For conditional text, use an IF formula as shown earlier: =IF(B2.

Does this work in Google Sheets?

Yes, the formulas are identical. In Google Sheets, TODAY() and date subtraction work the same way. The only difference is that Google Sheets may use a different date serial base (1900 vs 1904), but that rarely affects day differences.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.