How to Calculate Overdue Days in Excel (8 Suitable Examples)

Whether you’re creating a payment tracking workbook, invoice schedule, or assignment submission sheet, calculating overdue dates is a common necessity. Combining Excel’s subtraction method with date-related functions is an easy way to calculate the days past the due date.

Formatting Dates for Appropriate Date Calculations

In our dataset, we have a payment worksheet with columns for assigned tasks, initial deadlines, and actual submission dates. We’ll count the overdue days or how many days past the deadline were the submissions made in column E(Overdue Days).

For any calculation, it’s essential to format your dates so that Excel recognizes them and gives the correct results. For this, follow these steps:

➤ Select the cells with dates and go to the Home tab. From the ribbon, navigate to the Number group and click on the tiny downward arrow (launch icon).

➤ From the Format Cells box, go to the Number tab and select Date.

➤ Choose your preferred date format and click Ok. We chose the dd/mm/yyyy date format.
➤ If your preferred format isn’t in the list, go to Custom and enter the format in the Type box.

A simple subtraction formula can count overdue days from the due date to today. For this, we’ll use the TODAY() function that returns the present date, which is 05/06/2025 for our dataset. Here are steps to use it:

➤ Choose a cell in the overdue days column and type the following formula:

➤ Replace C2 with the cell reference of the due date.
➤ Click Ok and use the fill handle (black + sign on the bottom corner) to drag it down to the rest of the cells of the column.

In this case, we’ll use a column for due dates and a different date column representing the completion date. It allows us to count how many days were overdue between the two dates. Follow the steps given below:

➤ In a cell neighboring to the two dates, enter this formula:

➤ Here, C2 is the due date and D2 is our actual submission date up to which we’ll count the overdue days. Replace the cell references according to your dataset.
➤ Press Enter and drag the formula down to autofill the remaining cells with the correct overdue days.

Whether you want to count overdue dates from the due date to today or a different date, you can use the DAYS function to extract the days from a date.. Here’s how:

➤ Choose an overdue date cell and insert either of the following formulas:

Or,

➤ Here, C2 contains the due date and D2 contains the date up to which you want to count the overdue dates. Change the cell references according to your dataset.
➤ Press Enter and apply the formula to the remaining cells using the fill handle.

With the DATEDIF function, we can calculate the total days difference between two dates. As the formula returns a date, we need to use a suitable argument (“d”) to count the days only. Below are the steps for the function to count overdue days:

➤ Enter any of the formulas given below in a cell from the overdue days column:

Overdue Days from Due Date to Today

Overdue Days Based on Two Dates

➤ Instead of C2 and D2, use the cell references for the due date and the corresponding comparable date from the second date column.
➤ Click Enter and apply the formula for the other cell columns with the fill handle.

If the due date hasn’t passed, subtraction might return a negative value or an error. To avoid such negative values and replace them with zero(0), follow these steps:

➤ Select a cell to count the overdue days and enter either of the formulas given below:

Overdue Days from Due Date to Today

Overdue Days Based on Two Dates

➤ Replace the cell references based on your dataset and press Enter.
➤ Use the fill handle to count overdue days for the following cells.

To exclude weekends (Saturday & Monday) and calculate overdue days with the working days only, you can combine the NETWORKDAYS function with a subtraction formula. Below is the procedure:

➤ Pick an appropriate cell in the column for overdue days and insert the suitable formula from below:

Overdue Days from Due Date to Today

Overdue Days Based on Two Dates

➤ Change the cell references correctly, considering C2 as the due date cell and D2 as the date to which we’re counting the overdue days.
➤ Click Enter and count overdue days for the remaining dates by using the fill handle to drag the formula.

Using the IF function provides more flexibility in deciding what to display in the overdue cells. You can add custom texts or display an empty string for negative overdue days. Here are the details:

➤ Choose a cell in the overdue column and type any of the following formulas:

Overdue Days from Due Date to Today

➤ To return positive numbers for overdue items and zero(0) for items not yet due:

Overdue Days Based on Two Dates

➤ To return positive numbers for overdue items and zero(0) for items not yet due:

➤ Replace 0 with “” to return an empty string. In both cases, use the cell reference containing your due date instead of C2. The cell reference for the other date will replace D2.
➤ Press Enter and autofill the remaining cells using the fill handle.

To display # days overdue for positive overdue items and Not yet due for timely submissions and negative results, follow the steps given below:

➤ Choose a cell to display the overdue days and enter either of the following formulas:

Overdue Days from Due Date to Today

Overdue Days Based on Two Dates

➤ Instead of C2 and D2, use the appropriate cell references for your due date and actual submission date. You can also change the text according to your choice.
➤ Click Enter and drag the formula down using the fill handle.

Frequently Asked Questions

How do you handle error values when calculating overdue days?

Sometimes, due date cells might be empty or contain non-date values, leading to errors (#VALUE!). You can use the IFERROR function to handle these. Insert the following formula to get an empty string instead of an error:

=IFERROR(MAX(0, TODAY() – C2), “”)

You can use the original due date or a second date instead of TODAY().

How do you convert text to date in Excel?

Using certain functions and calculations might turn dates into text strings. To convert text into date, choose a cell adjacent to the text string and enter the following formula:

=DATEVALUE(C2)

Here, C2 is the cell reference containing the text. Change it according to your data or combine it with other formulas if you prefer.

How do I remove time from a date in Excel?

Start by selecting the cells containing the dates with time. Right-click on the selected cells and choose Format Cells. In the Format Cells dialog box, click on Date from the Category group. Choose a date format that does not include time (such as 3/14/2012 or 14-Mar-12). Finally, click Ok to display only the date while the cell retains the full value with time.

Final Words

Depending on your data, counting overdue days can be done with a basic subtraction formula. However, for more complex datasets and better control over the results, using the DATEDIF or IF function is a reliable way.

If you get an error from a formula, it probably means the due date hasn’t passed yet. Add the =IFERROR function to get rid of the error.