Hello Excellers and welcome back for aonther#formulafriday. Today I want to share how to highlight new entries into an Excel list within a specified time period. So, your boss has asked you send on a list weekly of new customers, and highlight them. This method will allow you to just send the list and Excel will do the hard work. Excel will automatically highlight the previous 7 days new entries or sign-ups. We can easily achieve this with some clever Conditional Formatting. And of course, this is #formulafriday so it does mean we are going to use a formula to determine which cells to highlight for your boss.
Let’s take an example sample. I have an extract of customers in Columns A through to B and their signup dates in Column C. We want to highlight last weeks new customers which at this point in time would be the last seven PRIOR days to 24 September 2018.
The Solution With Conditional Formatting.
The solution to this request from your boss if to simply use Conditional formatting to identify the dates in Column C which are seven days before the current date. This, of course, is assuming you are running the report on Monday. But even if you do not run the report Mondays, then just be aware that the conditional formatting will highlight seven days before the current date. Ok, so let’s get formatting.
- Highlight the data that you want to conditionally format.
- Home Tab | Styles Group | Conditional Formatting | New Rule
- In the Select Rule Type options use the ‘Use a formula to determine which cells to format’
- Type the following formula
=TODAY()-C2<7
So what exactly is that formula doing????.
Well, it is taking the TODAY function which returns the serial number of the current date. That is it. When we then use it with other data it becomes even more useful. in our example, we have taken Today’s date, subtracted the date ( or serial number) in Column C then applied the logic of fewer than seven days. If the condition is TRUE we format the cells in whatever way we choose to. Nice huh?.
We can break this down even more. Let’s take the first part of the formula.
=TODAY()-C2. This returns 25 as there are 25 days between these two dates. This means when we apply the logic of the third part of the formula =TODAY()-C2<7, it is most definitely not less than 7.
Bonus Extra Excel Tip.
If you are using conditional formatting, then sometimes it useful to practice the formula right in the Excel cells. This was you can get a TRUE or FALSE result to make sure your logic is working as expected. I use this method regularly. you can see the result of TRUE/FALSE test in the cells below.
What Next? Want More Tips?
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.
