Priti
Microsoft Excel is an excellent tool for performing any calculations. This article will demonstrate how to calculate overdue days in excel. Overdue refers to events that do not occur within a specified period. Thus, it is considered overdue if something does not happen or is not completed within the specified period. Suppose for any project, and you need to check how many employees have not submitted to the project yet. In addition to that, you want to know how many days they are due. For that, this article will come in handy for calculating overdue days in excel. In this article, we will demonstrate 4 easy formulas; three are Formulas, and one is VBA code to perform this calculation efficiently.
📁 Download Excel File
You can download the Excel file used for the demonstration from the link below.
Calculate Overdue Days in Excel with These 4 Quick Approaches
Firstly, let us get acquainted with our dataset used throughout the article as an example. This spreadsheet contains the Names of employees, Dates of submission of their work, and the last date of submission of the project. We want to calculate how many days are overdue for each employee. We will use this dataset to demonstrate 4 easy formulas to calculate overdue days in Excel.
📕 Read More: How to Calculate Average Tenure of Employees in Excel
Using IF & DATEDIF Functions
In this method, we demonstrated calculating overdue days in excel using IF & DATEDIF function. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want to calculate the overdue days. Here, we are selecting cell D5.
- Now, enter the formula given below in the cell.
=IF(ISERROR(DATEDIF(C5,$D$12,"d")),C5-$D$12&" Days Overdue","No Overdue")
🔨 Formula Breakdown
👉 Here, we used the DATEDIF function, which returns the difference between the target date (cell C5) and the last date of submission (cell D12) in days format. In addition, the ISERROR function checks any error in the DATEDIF function and returns TRUE or FALSE. Lastly, if the prior ISERROR function returns TRUE, the function returns the calculated overdue value and returns the “No Overdue” string if FALSE.
- Then, press Enter.
- After that, copy and paste this formula into all other cells where you want to calculate overdue days. For that, select cell D5 again and click & drag the Fill Handle.
- Or, Double-click on the plus that appeared after selecting the cell.
- Finally, you will get your desired outcome.
📕 Read More: 4 Examples to Calculate Dividend Growth Rate in Excel
Applying IF & DAYS Functions
Next, we will look at the formula applying the IF & DAYS functions to calculate overdue days in Excel in this method. Let us demonstrate the steps,
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell D5.
- Then, insert this formula in the cell.
=IF(C5>$D$12,DAYS(C5,$D$12) & " Days Overdue","No Overdue")
🔨 Formula Breakdown
👉 The function DAYS calculates the difference between inserted dates in two cells. Now, IF(C5>$D$12,DAYS(C5,$D$12) & ” Days Overdue”,”No Overdue”) formula firstly compares the target date and submission date. Next, if the target date is greater, calculate their difference using the DAYS function and show the value concatenated with the string “Days Overdue.” Unless it displays “No Overdue.”
- After that, press Enter.
- Lastly, copy and paste this formula into all other cells where you want to calculate overdue days. For that, select cell D5 again and click & drag the Fill Handle down, or Double-click on the plus that appeared after selecting the cells.
📕 Read More: 8 Ways to Calculate Age Using Formula on a Specific Date in Excel
Using IF function
This method will use IF function to calculate overdue days. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, select cell C5.
- Then, enter this formula in the cell.
=IF(C5=$D$12,"Submitted Today",IF(C5<$D$12,"No Overdue",(C5-$D$12)&" Days Overdue"))
🔨 Formula Breakdown
👉 The formula IF(D5=$D$13,” Submitted Today”,…) determines whether the value in cell C5 equals the value in cell D12. The function returns the string “Submitted Today” if it matches. If not, then this formula (C5<$D$12,”No Overdue”,(D5-$D$13)&” Days Overdue”) occurs. This function checks if the value in cell C5 is greater or lesser than in D12. If it is less, then it returns “No Overdue”. Otherwise, it returns the difference in days with the string “Days Overdue.”
- After that, press Enter.
- Finally, select cell D5 again and click and drag the Fill Handle down, or simply Double-click on the plus.
📕 Read More: 5 Quick Ways to Use Excel Formula to Count Days from Date
Applying VBA
In this method, we will calculate the overdue days in excel by applying Microsoft Visual Basic Application (VBA).
- Before starting, ensure you have the Developer tab in the ribbon. For example, here we do not have the Developer tab.
- First, go to the File tab.
- And select Options.
- Then a box named Excel Options will appear.
- From there, select Customize Ribbon, and on the right side, under Customize Ribbon, under Main Tabs, click on the Developer option and press OK.
- After that, the Developer tab will be available on the ribbon.
Now we can proceed to show the steps to applying VBA code.
⬇️⬇️ STEPS ⬇️⬇️
- First, go to the Developer tab in the ribbon.
- Next, from the code group select Visual Basic.
- Then Microsoft Visual Basic for Applications named window will appear. Select Module from the Insert.
- After that, insert the following Code.
Sub calc_overd_days()
Dim cellCount As Integer
Dim K As Integer
Dim last_date As Date
last_date = Cells(12, 3)
For cellCount = 5 To 10
If Cells(cellCount, 3).Value = last_date Then
Cells(cellCount, 4).Value = "Submitted Today"
ElseIf Cells(cellCount, 3).Value > last_date Then
K = last_date - Cells(cellCount, 3).Value
K = Abs(K)
Cells(cellCount, 4).Value = K & " Days Overdue"
Else
Cells(cellCount, 4).Value = "No Overdue"
End If
Next cellCount
End Sub- Lastly, press F5 to run the code.
📄 Important Notes
🖊️ Excel takes date input automatically in Month/Day/Year format. So, make sure to input dates in the proper format.
🖊️ To apply VBA codes, first ensure you have the Developer tab in the ribbon before the application.
📕 Read More: 5 Ways to Use Formula to Find Next Month in Excel
📝 Takeaway from This Article
📌 This Article demonstrates four easy formulas to calculate overdue days in Excel.
📌 In the first method, we showed a formula containing IF & DATEDIF functions to calculate overdue days.
📌 Then we demonstrated a formula that applies IF & DAYS functions to perform the calculations of overdue days.
📌 After that, to calculate the overdue days, we combined IF & SUBTRACTION functions.
📌 Lastly, we showed the steps to applying VBA code for the calculation easily.
Conclusion
This article has demonstrated four easy formulas to calculate overdue days in excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
