OmniDocFlag Overdue Dates in Excel With TODAY Formulas
Learn how to compare each due date with TODAY() to flag overdue tasks in Excel, skip finished items, and count what is still late.
A task list with due dates is only useful if you can see what is late. Many people track deadlines in a spreadsheet, then scan the dates by eye and miss the ones that slipped last week. If you have landed here while searching for excel formulas for dates overdue, the fix is a small set of formulas that compare each due date with today and label only the tasks that are still open.
This article shows how to build that flag. You will add a status column that reads "Overdue" when a due date has passed and the task is not complete, highlight the whole row so late items stand out, and count how many open items are overdue. The method uses standard Excel functions, so it works in any spreadsheet app that supports them.
If your task list currently lives in a PDF, the PDF editor in the OmniDoc desktop client can export it to Excel, and you can add the formulas after that. The steps below assume you already have, or are about to create, a spreadsheet with due dates.
Which formula flags overdue dates in Excel? The short answer
Use an IF formula that checks whether TODAY() is later than the due date and whether the Completed cell is still empty. The formula returns "Overdue" for late open tasks and nothing for everything else.
| Your situation | What to do | Why |
|---|---|---|
| You want a visible label per row | Add a status column with =IF(OR(LEN(B2)=0,LEN(C2)>0),"",IF(TODAY()>B2,"Overdue","")) | It flags only open tasks that have a due date and have passed it |
| You want the whole row to stand out | Add a conditional formatting rule with a formula | Color draws the eye to late items without reading each status |
| You need a count for a report | Use SUMPRODUCT to count rows where the due date has passed and Completed is empty | It counts overdue items without you filtering by hand |
| The list will be shared as a PDF | Export after you check the flags on that day | TODAY() changes daily, so the PDF shows the state on the export date |
| Some dates were pasted in as text | Re-enter them as dates before writing formulas | Text does not compare with TODAY() the way real dates do |
Before you start
Check these items first. Each one prevents a common reason the flags look wrong.
- Confirm the due dates are real dates. Dates that are left-aligned or show a green corner marker are often text. Text values sort and compare differently, so an overdue task can be missed. Re-enter them in a date format if needed.
- Pick a fixed column layout. This method assumes column A holds the task name, column B the due date, and column C the completed date (or blank). If your layout differs, adjust the letters in every formula.
- Decide what "done" means. The formulas treat any value in the Completed column as finished. If you use a status word instead, change the test so it matches your wording.
- Set a range that covers future rows. The count formula below uses rows 2 to 200. Choose an end row that is larger than the number of tasks you expect, so new rows are included.
- Know that TODAY() is not frozen. The function returns the date when the file is calculated, so flags change on their own from one day to the next. This is useful for a live tracker and a problem for a snapshot.
Building the overdue flag, step by step
- Set up three columns. Label them Task, Due date, and Completed. Enter one task per row, starting in row 2, and type a due date in column B for each task.
- Leave Completed blank for open tasks. When a task is finished, type its finish date in column C. Blank means open, and a value means done.
- Add the status formula. In column D, row 2, enter
=IF(OR(LEN(B2)=0,LEN(C2)>0),"",IF(TODAY()>B2,"Overdue","")). The outer test returns blank for rows with no due date or a completed date, and the inner test returns "Overdue" only when today's date is after the due date. - Copy the formula down. Fill column D to the last task row. Each row now checks its own due date and completion status.
- Highlight the whole row. Select the task range, such as A2 to D200, and add a conditional formatting rule that uses a formula. Enter
=AND(LEN($B2)>0,TODAY()>$B2,LEN($C2)=0)and set a fill color. Write the formula for the first row of the range, and keep the dollar signs on the column letters so the rule applies correctly to every row. - Count the overdue open tasks. In a summary cell, enter
=SUMPRODUCT((LEN(B2:B200)>0)(LEN(C2:C200)=0)(TODAY()>B2:B200)). This counts rows that have a due date, have an empty Completed cell, and whose due date is earlier than today. The first test keeps blank due dates out of the count. - Sort by due date if you want the late items at the top. Sort the table by column B, oldest first. Overdue tasks will sit above tasks that are still on schedule.
- Save and reopen the file on a later day. Confirm the flags and the count have changed as the dates passed. This step is the test that the formulas respond to TODAY() and not to a value you typed in.
Check the result
Once the formulas are in place, check the output against the tasks you know about. Use this table to find and fix common problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Due dates are real dates | Use =ISNUMBER(B2) in a spare cell; it should return TRUE | Re-enter the date in a date format, then fill the formula down again |
| Blank rows stay blank | A row with no due date shows no status and no highlight | Confirm the OR test in the status formula includes the blank check on B |
| Finished tasks lose the flag | Type a date in Completed for an overdue row | Check that the formula points to column C and that the completed cell is truly empty |
| The count matches the list | Count the overdue rows by eye with the flags visible | Make sure the SUMPRODUCT ranges cover every task row and the end row is large enough |
| The flags update on another day | Reopen the file on a later date and compare | If nothing changes, the formulas may be pasted as values; re-enter them |
If you share the tracker as a PDF, remember that the flags reflect the day you exported it. Re-export on the day you need the status.
❓ FAQ
1. Can I keep the overdue flags from changing every day?
Yes, but you will lose the automatic update. Type a fixed "as of" date in a cell, such as G1, and replace TODAY() in your formulas with that cell reference. The flags will then show the status for that date until you change it.
2. Does TODAY() count the time of day?
No. TODAY() returns only the date, so a task due today is not marked overdue until the next day. If your due dates include a time, the comparison still treats them as dates at midnight.
3. How do I count overdue tasks for one person?
Add a test for the owner's name to the SUMPRODUCT formula. For example, =SUMPRODUCT((A2:A200="Ana")(LEN(B2:B200)>0)(LEN(C2:C200)=0)*(TODAY()>B2:B200)) counts only the open, overdue tasks assigned to Ana, provided the owner name is in column A.
4. Why do I see "Overdue" on a row with no task name?
The status formula checks only the due date and the Completed cell. If a row has a due date but no task name, it will still be flagged. Either fill in the task name or clear the due date for that row.
📝 Summary
The simplest way to handle excel formulas for dates overdue is an IF formula that compares each due date with TODAY() and ignores finished tasks. Add conditional formatting for visibility and a SUMPRODUCT formula for totals. Check that your dates are real dates, and re-export any PDF on the day you need the status.
