How to Calculate Overdue Days in Excel (4 Easy Ways)

Today I’m going to show you how to calculate overdue days in Excel. Calculating overdue is a staple for any form of resource management.

For this session, I’m using Excel 2019, you can use your working version. All the methods used here will work on any version of Excel, starting from Excel 2003.


Download Practice Workbook

I have shared the workbook, you can download it from here.


Some Basics About Dates in Excel

Before we dive deep into the methods, here are some basics you might want to know.

Excel is very friendly, but very in-depth when it comes to the matter of dates. You can input dates in several formats. And it will automatically understand whether the value is a date or not, though for that you have to write in a specific format.

Typically, Excel takes the input of dates in the format of Month/Day/Year. If you write any value in this format, Excel will count that as a date.

Suppose, I have a date of 11 February 2021. Now in Excel, we are writing it in the format of 11 Feb 2021.

As soon as we hit Enter, Excel will present us with the date in a more specific way.

That’s cool! Isn’t it?

Now you have a couple of date formats in Excel. Short Date, Long Date.

date formats

In Short Date format, Excel will provide you the Date in MM/DD/YY format.

DD for Day

MM for Month

YY for Year

Each section will be of two digits.

In Long Date format, you will be provided with the name of the day as well. Here, we are going to set the Long Date format for the date we input earlier.

long date format

Look, our input remains the same, but Excel is displaying the day’s name as well.

You can choose your date format as well. Just explore the Number section on Home Tab and select More Number Formats like the figure below.

more number formats in excel

There you will find a dialog box. You can select or insert your desired format there.

For some date calculations, you will find some functions in Excel. Today we’re going to see a couple of them.


What Does Overdue Mean?

Overdue means the things that fail to happen within a given period. So, if something has not happened or has not been done within an expected period, then it’s overdue.

Let’s imagine, you had a task that needed to be finished yesterday, but instead, you finished it today. So, your target day was yesterday, and your finishing time is today. So, you exceeded the limit by 1 day, which is overdue. Your overdue day is 1 here.


4 Easy Ways to Calculate Overdue Days in Excel

As mentioned before, we need to evaluate first if a date is in the overdue category before any calculations first. There are many functions to find date differences. But to use them with the overdue check, we can use a handful of them. Below, we have described those functions, how to use them, along with their breakdowns. In the end, we have also added a VBA code to calculate overdue days in Excel, if that suits your needs.

We will be using the following dataset for the demonstration.

calculate overdue days in excel dataset

There are different submission dates for different persons. All of which had one submission date. With the help of these methods, we are going to determine if those days are overdue or not in the first place and then calculate the overdue days in Excel.


1. Using IF with Subtraction Formula

In the first method, we will be utilizing the IF function and the basic subtraction formula. Just subtract two dates. But wait a minute, do we know from which value we have to subtract? To evaluate that we will use the IF function.

The IF function will show us which date will be Minuend and which will be Subtrahend.

The minuend is the number, that is to be subtracted from

The subtrahend is the number, that is to be subtracted

Follow these steps to see how we can use the function to calculate overdue days in Excel.

📌 Steps:

  • First, select the cell you want to insert the calculation result. Here, we are selecting cell E5.
  • Then insert the following formula in it.

=IF(D5=$D$13,"Submitted Today",IF(D5<$D$13,"No Overdue",(D5-$D$13)&" Days Overdue"))

if with subtraction formula to calculate overdue days in excel

🔎 Breakdown of the Formula

IF(D5=$D$13,”Submitted Today”,IF(D5<$D$13,”No Overdue”,(D5-$D$13)&” Days Overdue”))

👉 IF(D5=$D$13,”Submitted Today”,…) formula checks if the value in cell D5 matches with the cell value of D13. In case it does match, the function returns the string “Submitted Today”. Else it moves on to the next part of the formula.

👉 IF(D5<$D$13,”No Overdue”,(D5-$D$13)&” Days Overdue”) occurs when the previous function returns a FALSE on the boolean check. This function then checks if the cell value of D5 is less than D13. If it is, then it returns the string “No Overdue”. Otherwise, it returns the difference of the days with the string “ Days Overdue” attached to it.

  • After that, press Enter on your keyboard.

  • Now select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

calculate overdue days in excel using if and subtraction formula

This is how we can easily calculate overdue days in Excel.


2. Utilizing IF and DAY Functions

The DAYS function returns the difference between two dates. The function has two parameters, start_date, and end_date. Combining this function with the previously mentioned IF function can create a combination that can check for overdue days and calculate them in Excel.

Follow these steps to see how that works.

📌 Steps:

  • First, select cell E5.
  • Then write down the following formula.

=IF(D5>$D$13,DAYS(D5,$D$13) & " Days Overdue","No Overdue")

🔎 Breakdown of the Formula

👉 DAYS(D5,$D$13) calculates the difference in days between two dates that belong to cells D5 and D13.

👉 IF(D5>$D$13,DAYS(D5,$D$13) & ” Days Overdue”,”No Overdue”) function checks if the cell value of D5 is greater than D13. Then it returns the DAYS function value concatenated with the string “ Days Overdue” if the logical operator is true. Otherwise, it returns “No Overdue”.

  • After that, press Enter.

formula with if and days function to calculate overdue days in excel

  • Next, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

calculate overdue days in excel utilizing if and days functions

This is another way you can calculate overdue days in Excel.


3. Combining IF with DATEDIF Function

The DATEDIF function is not considered a standard function. And so it is not part of the function library or any documentation. Usually, Microsoft discourages using this function as it can mess up results with errors in some cases. But if we combine these errors with the ISERROR function along with the IF function, we can create a workable combination that can identify and calculate overdue days in Excel.

Follow these steps to see the formula and its usage in the sample dataset.

📌 Steps:

  • First, select cell E5.
  • Then insert the following formula.

=IF(ISERROR(DATEDIF(D5,$D$13,"d")),D5-$D$13&" Days Overdue","No Overdue")

calculate overdue days in excel with a combination including datedif function

🔎 Breakdown of the Formula

IF(ISERROR(DATEDIF(D5,$D$13,”d”)),D5-$D$13&” Days Overdue”,”No Overdue”)

👉 DATEDIF(D5,$D$13,”d”) returns the difference between cells D5 and D13 in days format.

👉 ISERROR(DATEDIF(D5,$D$13,”d”)) checks if there was any error in the DATEDIF function. It returns a TRUE or FALSE value depending on whether there was an error or not.

👉 Finally, IF(ISERROR(DATEDIF(D5,$D$13,”d”)),D5-$D$13&” Days Overdue”,”No Overdue”) returns the calculated overdue value if the previous ISERROR function returns true. Otherwise, it returns the string “No Overdue”.

  • After that, press Enter.

  • Now select the cell again and click and drag the fill handle down to replicate the formula for the rest of the cells.

calculate overdue days in excel combining if with datedif functions

This is how we can utilize the DATEDIF function to our advantage to calculate overdue days in Excel.


4. Embedding VBA Code

Microsoft Visual Basic for Applications (VBA) is an event-driven programming language developed by Microsoft. This is built into most Microsoft Office applications. Along with them, Excel contains VBA too that can assist us with minimal tasks to automate large boring procedures. We can also use VBA to our advantage here to identify and calculate overdue days in Excel.

To use any form of VBA code, we need the Developer tab on the ribbon to show. If you don’t have one on your ribbon, click here to display the Developer tab on your ribbon. Once you have that, you can follow these steps to use the code to calculate overdue days in Excel.

📌 Steps:

  • First, go to the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group section.

  • Now click on the Insert tab and select Module from the drop-down to insert a module.

inserting module in vba window

  • Next, select the module if it isn’t selected already.
  • Then insert the following code into the module.
Sub overdue_days()
Dim cell As Integer
Dim J As Integer
Dim due_date As Date
due_date = #1/11/2022#
  For cell = 5 To 11
    If Cells(cell, 4).Value = due_date Then
       Cells(cell, 5).Value = "Submitted Today"
    ElseIf Cells(cell, 4).Value > due_date Then
        J = due_date - Cells(cell, 4).Value
        J = Abs(J)
        Cells(cell, 5).Value = J & " Days Overdue"
    Else
       Cells(cell, 5).Value = "No Overdue"
    End If
  Next cell
End Sub
  • Finally, press F5 to run the code instantly.

calculate overdue days in excel using vba

This way, we can use VBA to our advantage and identify and calculate overdue days in Excel.


Overdue Days Calculator

You can use today’s practice workbook as a calculator to count overdue days.

You will find a sheet called Calculator in the workbook.

Here, we have converted the dataset with the formula from the first method into a table. So that, when we enter a new value in the end it automatically includes the formats with the formula in the new row. Thus you can use the workbook as a calculator.


Conclusion

That’s all for the day. I’ve listed a couple of ways to calculate overdue days in Excel. Hope that will help you. Feel free to comment if something seems hard to understand. Let us know which of the methods you are going to use. You can write your own way of calculating overdue days.

For more guides like this, visit Exceldemy.com.

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo