How do you get a cell in Excel to change color when a date expires 2019?

Hello Excellers and welcome back to another #Excel tip in my 2019 series of #formulaFriday. Do you need to highlight expired dates? or any dates that are before the current date?.

How do you get a cell in Excel to change color when a date expires 2019?

Hello Excellers and welcome back to another #Excel tip in my 2019 series of #formulaFriday. Do you need to highlight expired dates? or any dates that are before the current date?. Well, this Excel tip will help you do that easily. In my example today I will show you step by step how to use the TODAY() function with some conditional formatting to highlight expired dates or dates before today. Lets get started!.

Sample Data Set.

Here is my sample data set. I have an index or reference of paper records, their location and the date of expiry. These relate to records that need to be destroyed after the date of expiry is before the current days date.

highlight expired dates in Excel
Pin it!
Share on Facebook

So, I need to identify those records that require removing from my records section and destroy them. I will highlight the Expiry date, only if it is after the current or todays date. So, lets get on with writing our formula to enable our conditional formatting.

Conditional Formatting Using A Formula.

The solution we are going to use today uses a formula to set the conditional formatting rules. Now, Excel does come already packed with a stack of pre-defined rules to use. If you want to check them out then you can see them in the following location

Home Tab | Style Group | Conditional Formatting

highlight cells with conditional formatting  and the today function
Pin it!
Share on Facebook

As I want to test the condition before or equal to Todays date I want to use a formula. This gives me more scope to decide my rules to conditionally format my cells.

The Today Function To Highlight Expired Dates.

The Excel TODAY function is simple. The syntax for a quick recap is as follows

=TODAY()

There are no arguments for the TODAY function. See, didnt I tell you it was simple?. Once you type this function into Excel then todays date is displayed. We can, therefore, use it in conditional formatting to test if the condition is TRUE or FALSE. This is how we conditionally format cells.

  • Select the cells to conditionally format.
  • Home Tab | Styles Group| Conditional Formatting.
  • New Rule | Use a formula to determine which cells to format.
  • In the Edit, Rule dialog box type the following formula

=C2<=TODAY()

  • The final stage is to set your formatting. I have chosen a Red fill.
highlight cells with today and conditional formatting
Pin it!
Share on Facebook

The result of my data set with the conditional formatting can be seen below. Todays date is 29 September so it has correctly conditionallty formatted record YRD0093. I need to go to Section 123 and destroyt this record.

expired dates conditional formatting
Pin it!
Share on Facebook

Thats it. it works i can now highlight expired dates in my Excel data set.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below.

How To Excel At Excel  Formula Friday Blog Posts.

th
Learn Excel Dashboard Course

So, Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month. Just click on the link and enter your email. No spam. Just Excel tips.

Finally, I am delighted to let you know I have teamed up with Excel Rescue. Have you got a problem with Excel?. Get Help with Excel  Fast! Excel Rescue is a done-for-you small tasks service for Microsoft Excel. Why dont you check out how they can help you. Click on the link below for special offers on Excel solutions. Book mark the link for furture reference.

ExcelRescue.net
Udemy.com Home page 125x125

Video liên quan