How to calculate days left until deadline in Excel?
In daily work and project management, tracking how many days remain before a deadline is critical. For instance, you may have a list of deadline dates and need to monitor the days left for tasks, assignments, contract expirations, or project milestones. Calculating the remaining days efficiently helps ensure that important dates are not missed and supports better scheduling and time management. This article explains practical methods to calculate the days left until a deadline in Excel, providing step-by-step guidance and solutions for different needs.
- Calculate days left until deadline with formulas
- VBA Code - Automate days left calculation and optionally highlight or notify overdue deadlines
Calculate days left until deadline with formulas
If you want to quickly determine the number of days remaining before each deadline, Excel provides simple formula solutions. These are suitable for static lists or when regular recalculation based on the current date is required.
1. In a blank cell where you want the result displayed (for example, in column B beside your deadline date in column A), enter the following formula:
=A2-TODAY()This formula subtracts today’s date (as returned by TODAY()) from the deadline date in cell A2. The result is the number of days remaining.
After entering the formula, drag the fill handle down to apply this calculation for additional rows containing deadline dates, as shown below:
2. To ensure your calculation results are displayed as numbers, format the result cells as General. Click the Home tab > locate Number Format drop-down > select General, as shown below:
Notes & Practical Tips:
- If the deadline date has already passed (is earlier than today), the result will be a negative number, indicating the number of days overdue.
- If you want to avoid displaying negative values (for example, showing 0 for overdue tasks), use this alternative formula:
=MAX(0,A2-TODAY())This formula compares the calculated days remaining with 0 and displays whichever is greater—effectively replacing negative numbers with 0.
This approach is especially useful when reporting deadlines where only the remaining (non-negative) days are of interest, such as open tasks or upcoming contract expirations.
- Always check that your deadline dates are valid Excel date values. Incorrect or text-format dates will result in calculation errors or unexpected results.
- Formulas using
TODAY()will automatically update every time you open or recalculate your worksheet. - If you prefer to work with business days (excluding weekends and holidays), consider using the
NETWORKDAYSfunction for a different scenario.
Regularly reviewing your deadline list and recalculating days remaining can significantly improve your ability to anticipate and act on upcoming deadlines. If you require more advanced automation or visual reminders, see the VBA solution below.
VBA Code - Automate days left calculation and optionally highlight or notify overdue deadlines
For users who deal with extensive deadline lists or wish to streamline the process, automating the days-left calculation with VBA can substantially reduce manual effort. This method is particularly valuable when you want Excel to perform additional actions such as automatically highlighting overdue deadlines or providing a notification, ensuring that important dates are never overlooked.
1. To use this method, enable the Developer tab if it’s not already visible (File > Options > Customize Ribbon > check Developer). Go to Developer > Visual Basic to open the VBA Editor. In the VBA window, click Insert > Module and enter the VBA code below:
Sub CalculateAndHighlightDaysLeft()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim deadlineCol As String
Dim resultCol As String
Dim daysLeft As Long
Dim cell As Range
On Error Resume Next
' Configure columns as needed below:
deadlineCol = "A" ' Column containing deadlines
resultCol = "B" ' Column to place days left calculation
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, deadlineCol).End(xlUp).Row
For i = 2 To lastRow
If IsDate(ws.Cells(i, deadlineCol).Value) Then
daysLeft = ws.Cells(i, deadlineCol).Value - Date
ws.Cells(i, resultCol).Value = daysLeft
' Highlight overdue deadlines
If daysLeft < 0 Then
ws.Cells(i, deadlineCol).Interior.Color = RGB(255, 185, 185) ' Light red fill
' Optionally show popup message for overdue (uncomment next line if desired)
' MsgBox "Row " & i & " is overdue! Deadline: " & ws.Cells(i, deadlineCol).Text, vbExclamation, "KutoolsforExcel"
Else
ws.Cells(i, deadlineCol).Interior.Pattern = xlNone
End If
Else
ws.Cells(i, resultCol).Value = "Invalid date"
ws.Cells(i, deadlineCol).Interior.Color = RGB(255, 235, 156) ' Yellow fill for invalid data
End If
Next i
End Sub2. Close the VBA Editor, return to your worksheet, and press Alt + F8. In the dialog that appears, select CalculateAndHighlightDaysLeft and click Run. The code will process each date in column A (from row 2 downwards) and write the days left in column B. Overdue deadlines (where the date is before today) will be automatically highlighted in light red. Cells with invalid dates will be filled in yellow and marked as "Invalid date" in the result column.
- You can modify deadlineCol and resultCol in the code if your deadlines are in a different column.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in