PrabasTech

Highlight or Trigger Expiry or Due Date using Excel

Control the Expiry or Due Date using Excel

Dealing with expiry or due date is one of the important task for most of the office goers. In this tutorial we have explained 3 methods to manage expiry or due date related tasks using Excel. Those are:

  1. Highlight
  2. SORT
  3. FILTER

Highlight Expiry Date before 30 days using Conditional Formatting

In this method, we are going to highlight only the due dates which is lesser than or equal to 30 days. So if the expiry or due date is lesser than 30 or equal to 30 days, that will be highlighted in the final output. Look at the sample data set below that we are going to work on.

Highlight Data using Conditional Formatting - Prabas Tech

Assume, the today date is 22 Aug 2020.

  • As per the sample data, we are going to highlight only few rows which has lesser than or equal to 30 days (Due Date – Today Date).
  • To support further process, we have to add the number of days between Due Date and Today Date in Column D.
  • Remaining days in D2 is = C2 – today(), which is 41 days. So, type in =C2-today() in D2. Then, drag the formula from D2 to D13 to fill the rest of the cells.

Highlight Data using Conditional Formatting

  • The numbers in column D gives us a clear picture of the remaining days between the current date and the due date. Now, we are going to highlight some of the Rows from Row 2 to Row 11, which is our ideal goal of this tutorial. 
  • Select the range A2:D2, and navigate to Home tab > Styles > Conditional Formatting.

Conditional Formatting

  • We able to highlight cells either from the first option (Highlight Cells Rules) or from the last option (Manage Rules) or from the middle one (New Rule) from the Conditional Formatting drop-down menu.
  • We may go with the the middle one which is New Rules
  • You able to see the New Formatting Rule dialog box with 6 Rule Types
  • Select the last one “Use a Formula to determine which cells to format”

Excel Conditional Formatting Window - Formula

  • We come into the main place where we need to type in the conditional formula. Here, type in =if($D2<=$F$5,True,False) and set your decried formats using the Format option in the same window. Then, select OK.
  • This formula checks the value in D2 against  F5, and highlight D2 if it passes the condition, otherwise it dose nothing. 
  • Remember, we have only applied Conditional Formatting on the first row (A2:D2). So, we have to expand and apply Conditional Formatting to rest of the rows.
  • To expand the created Conditional Formatting, Navigate to Home > Style > Conditional Formatting > Manage Rules

Conditional Formatting - Applied Rules

  • We have created only one Rule which is listed in the above window.
  • Select and change the existing range in the “Applies to” section 

  • Change the existing selection A2:D2 to A2:D13. Then select Apply and click Okay.
  • Now the final output should look like the below snapshot with three highlights, Row 3, Row 5 and Row 6.

Conditional Formatting - Final Output

  • Totally three highlights have developed as they are lesser than 30 days.
  • It changes automatically based on the remaining days in column D.

Sort and transfer the existing data set for bringing the closest expiry or due dates to the top using SORT function

SORT is one of the latest functions in Office 365. If you do not have O365, you should follow the following 3 tutorials to gain more ideas to manage such scenarios. 

If you are using Office 365, we strongly recommend you to go thorough the entire video tutorial linked below.

Filter existing data set to see only the closest expiry or due dates using FILTER function

FILTER is one of the latest formulas in Office 365. If you do not have O365, we will try to make a video tutorial for you to follow along.

If you are an Office 365 user, we strongly recommend you to go through the entire video tutorial linked below for better understanding.

Downloadable Excel

for Practice

I hope you have learned the entire concept.

Want to receive weekly updates tips & tricks

then register here

Chat
1
Join
PrabasTech Welcome you.
Would you like to join a course?