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
- Excel Biweekly Timesheet Template with Lunch
- X++ String Split: How to Use str2con and Other Methods
- Excel Before Close Event: How to Run Code on Save
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF: Count Values Greater Than a Number EasilyYes. To count values greater than a number with Excel COUNTIF, use a criterion such as ">100". If…
- How to Clear an Excel Sheet and Keep All FormulasYes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Find & Select…
- Excel Filter Cells Containing Text: Steps and OptionsTo filter Excel cells that contain a particular word or phrase, select your data, choose Data > Filter,…
- Count Unique Strings in Excel: Formulas and ExamplesTo count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- Excel Formula for an Entire Column: How to Use ItTo calculate values from an entire column, use a whole-column reference such as A:A inside a function. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.