How to Calculate Overdue Days in Excel (3 Methods)

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.

Before diving into the big picture, let’s know about the Excel sheet, which is the base of our example today.

Excel sheet - How to Calculate Overdue days in Excel

There are five columns: Submission Date, Due Date, Overdue Days, Using Function(DAYS), Using Function(DATEDIF). Using this worksheet we will calculate overdue days.

Practice Workbook

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

Basics You May Want to Know

Date Basics

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 input of dates in a format of Day-Month-Year (For my region this is how the date stands, it may vary according to your region). 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, I’m writing it in a format of 11-02-2021

Date basic Input-How to Calculate Overdue days in Excel

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

Date Basic-How to Calculate Overdue days in Excel

That’s cool! Isn’t it?

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

Date Format-How to Calculate Overdue days in Excel

In Short Date format, Excel will provide you the Date in DD-MM-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. I am going to set the Long Date format for the date I input.

Long Date - How to Calculate Overdue days in Excel

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

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

More Formats -How to Calculate Overdue days in Excel

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

Dialog box - How to Calculate Overdue days in Excel

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

Concept of Overdue

Overdue means the things that fail to happen within a given period. So, if something has not happened or not 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, that is overdue. Your overdue day is 1 here.

Many Ways to Calculate Overdue Days in Excel

1. Calculate Using the Minus/Subtraction (-) Formula

It is the basic subtraction formula. Just subtract two dates. But wait a minute, do we know from which value we have to subtract from? To evaluate that we will use a IF() function.

IF() function will show us which date will be Minuend and which will be Subtrahend

Minuend is the number, that is to be subtracted from

Subtrahend is the number, that is to be subtracted

The formula will be like this

IF(Submission Date<Due Date , Due Date- Submission Date, Submission Date – Due Date)   

IF() has three placeholders, separated by comma. First one is the logic check, second one is the result if logic comes true, third one is the result if logic comes false. 

Subtract date

Here, I’ve written the function, to check whether the Submission Date is smaller than the Due Date or not, if yes then subtract Submission Date from Due Date else subtract Due Date from Submission Date.

Do the same for the rest of the dates.

AutoFill

2. Calculate Overdue Days Using the DAYS Function

DAYS() function returns the difference between two dates. The function has two parameters, start_date, end_date.

DAYS Function

Here I’ve written the Due Date and the Submission Date in the function, separated by comma. And it gave the overdue days as result.

For the rest of the rows, you can write the functions repeatedly or simply use Excel AutoFill.

AutoFill DAYS

3. Calculate Using the DATEDIF Function

You can get the result by using the DATEDIF() function. DATEDIF() function has three parameters, start_date, end_date, and format.

DATEDIF Functions

Here I’ve written the function placing Submission Date and Due Date in two date placeholders and “D” in type placeholder.

D for days, since we need the difference in days format. You can use M or Y for the output in months or years respectively.

AutoFill DATEDIF

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.

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.

Calculator

Go to that sheet. You can calculate the overdue days from there.

Calculator Sheet

Insert your values in the respective fields, you will find the overdue days as result.


Further Readings

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