How to Highlight Due Dates in Excel Dynamically based on Current Date

How to Highlight Due Dates in Excel Dynamically based on Current Date


                                Excel's Conditional Formatting feature is incredibly powerful. It allows you to apply formatting to cells or ranges of cells based on criteria that you can choose. You can even use the value of other cells in the worksheet as part of your conditional formatting criteria.


Here we are discussing one of important application of conditional formatting for tracking due dates

Here we have scenario for tracking the due dates of payment of the customer based on real time and we need to add some scenarios for tracking the due dates



Add the day's option for the calculating by using data validation option.



We also need to update the status of payment done or not by adding the data validation option in the cell range




Select the range where we need to highlight the data, and click on the home tab and select to conditional formatting, select the new rule and select the option “Format only cell that contains”,

Select the Cell Value and select the criteria less than equal to “Today()+cell where we put days “

And click ok.




Now we need to remove highlight option based on the payment status for this option again we need to apply conditional formatting by selecting the range and click on the conditional formatting and click on new rule and select “Use a formula to determine which cells to format” and here we need to write formula “E7=” Done” and click on no format option and click OK

And we need to click on the conditional formatting option and click on manage rule and click check mark on the option “Stop If True” against the rule for the statement if the rule is true no need to format the cell.





Click here for downloading the workbook file

Video Link: Watch Video

Phone : +91 8606648045
Email :
Excel Skill Corner
Kerala, India.