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

Introduction to Dates in Excel

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.

We have a date of 11 February 2021. In Excel, we are writing it in the format 11 Feb 2021.

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

There are a couple of date formats in Excel, like Short Date, Long Date, etc.

In the Short Date format, Excel will provide you the date in MM/DD/YY format. In the Long Date format, you will be provided with the name of the day as well.

You can choose your date format as well. Go to the Number section on the Home tab and select More Number Formats like the figure below.

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

How to Calculate Overdue Days in Excel: 4 Easy Ways

We will be using the following simple dataset to calculate if a submission is overdue.

Method 1 – Using IF with Subtraction Formula

Steps:

• Select the cell you want to insert the calculation result. We are selecting cell E5.
• Insert the following formula in the cell:
`=IF(D5=\$D\$13,"Submitted Today",IF(D5<\$D\$13,"No Overdue",(D5-\$D\$13)&" Days Overdue"))`

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. Otherwise, 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.

• Press Enter on your keyboard.

• 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.

Method 2 – Utilizing IF and DAY Functions

Steps:

• Select cell E5 and insert 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”) This portion checks if the cell value of D5 is greater than D13. It returns the DAYS function value concatenated with the string Days Overdue if the logical operator is true. Otherwise, it returns No Overdue.

• Press Enter.

• 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.

Read More: How to Calculate Remaining Days in Excel

Method 3 – Combining IF with the DATEDIF Function

Steps:

• Insert the following formula in cell E5.
`=IF(ISERROR(DATEDIF(D5,\$D\$13,"d")),D5-\$D\$13&" Days Overdue","No Overdue")`

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 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.

• Press Enter.

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

Method 4 – Embedding VBA Code

Steps:

• To use VBA code, you need the Developer tab on the ribbon. If you don’t have one, click here to display the Developer tab on your ribbon.
• Go to the Developer tab.
• Select Visual Basic from the Code group section.

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

• Select the module if it isn’t selected already.
• 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``````
• Press F5 to run the code instantly.

Overdue Days Calculator

You will find a sheet called Calculator in the workbook. We have converted the dataset with the formula from the first method into a table. When we enter a new value, it automatically includes the formats with the formula in the new row. You can use the workbook as a calculator.

Related Articles

<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF