How to Use Formula for Past Due Date in Excel (3 Methods)

In times of data sorting, we sometimes face the necessity of finding past due date. From this article, you will know about Excel formula for past due date.

For clarification, I have used a dataset containing data titled Project Name and Deadline.

Excel Formula for Past Due Date


Download Practice Workbook


3 Ways to Use Formula for Past Due Date in Excel

1. Using Excel TODAY Function in Conditional Formatting for Past Due Date

Use of TODAY Function is a very efficient way to separate for Past Due Date.

Steps:

  • Select all the cells containing dates. Here, I have selected cells C5:C11.
  • Then, choose Home from the ribbon.
  • Select Conditional Formatting and New Rule sequentially.

A Dialogue box will appear.

  • Select Format only cells that contain from Select a Rule Type.
  • Next, From the Format only cells with, select Cell Value, less than and =TODAY() continuously.
  • Afterward, select Format. 

Excel Formula for Past Due Date

Another dialogue box will appear named Format Cells.

  • Select the Fill tab and choose a color.
  • Press OK.

  • Press OK again.

Excel Formula for Past Due Date

Finally, the formula will be applied and the past due date will be identified.

Read More: Excel VBA: Filter Date before Today (With Quick Steps)


2. Applying Excel IF & ISBLANK Functions for Past Due Date

We can also apply IF Function , TODAY function and ISBLANK function to create a formula for  past due date.

Steps:

  • Select a cell and input the formula.
  • Here, I have selected D5 for the application of the formula.

The Formula here is:

=IF(ISBLANK(C5),"", IF(C5<TODAY(),"Delayed","On Time"))

Where, ISBLANK Function is used to check if the selected cell is blank or not. A condition is applied using IF Function which gives Delayed output for value less than today compared with the selected cell value and On Time output for the rest.

Excel Formula for Past Due Date

  • Press ENTER.

Excel Formula for Past Due Date

Read More: How to Use IF Formula for Date Range in Excel (6 Methods)


Similar Readings


3. Employing Excel Combined Functions for Past Due Date

We can also employ a formula that contains both IF, TODAY & MAX Function for past due date.

Steps:

  • Select a cell and write the formula.
  • Here, type the following formula in cell D5.
=IF(MAX(0,TODAY()-C5)=0,"On Schedule","Due")

Where MAX Function returns the largest value and ignores the empty cells. A condition is applied here using IF Function where differentiating values between today’s date and the date mentioned in the cell, it will give output On Schedule or Due.

  • Press ENTER.

Excel Formula for Past Due Date

  • For the AutoFill, use Fill Handle.

Related Content: Excel Formula Date Range


Practice Section

You can practice here for more efficiency.

Excel Formula for Past Due Date


Conclusion

Every Excel Formula for Past Due Date is shown here is quite easily functional and effective. Here, you can give your feedback or further queries in the comment section.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

1 Comment
  1. Dear Sir,
    Hope you are doing well can you help me?
    Please tell me formula What can i use for alert in excel.
    1) How do I create a pop up alert in Excel?
    2) How do I blink or flash text of a specific cell in Excel?

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo