Excel - Highlight Open Items after 4 Days - Excel - Episode 1717

Microsoft Excel Tutorial: How can I highlight any date after 4 days - only if the next Cell has no data?

Welcome to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to solve your Excel problems. In this episode, we tackle a question sent in by Shahmeer about highlighting open items after 4 days. But, there's a catch - if there's a date or text in the next column, it should not be highlighted. Sounds tricky, right? Let's dive in and see how we solve this challenge.

First, I'll show you my approach. I select all the cells and set up conditional formatting using the formula =AND(TODAY()-A3＞4,ISBLANK(B3)). This formula checks if the date in A3 is more than 4 days old and if there's nothing in B3. If both conditions are met, the cell will be highlighted. This is a simple and effective solution that will save you time and effort.

Now, let's see how Mike tackles this problem. He takes a slightly different approach by using the formula =AND(A3＜=TODAY()-4,B3=""). This formula compares the date in A3 to today's date minus 4 days and checks if B3 is empty. Again, if both conditions are met, the cell will be highlighted. Mike also shares a useful tip - when building a formula, always consider the active cell and build the formula from its point of view. This will ensure that the formula works correctly, no matter where the active cell is.

But wait, there's more! Mike also explains the difference between using ISBLANK and ="", which both check for empty cells. ISBLANK only checks for truly empty cells, while ="", also known as a null text string, will not catch cells with a formula that delivers a null text string. So, it's important to choose the right one for your specific needs.

In conclusion, we hope this episode has helped you learn some new tricks and techniques for highlighting open items after 4 days. Don't forget to like, share, and subscribe to our channel for more exciting Excel tips and tricks. And as always, keep on Excelling!

Table of Contents:
(00:00) Highlight Open Items After 4 Days
(00:10) Sample data
(00:38) Conditional formatting formula
(02:12) Alternative formula
(02:45) Comparison of formulas
(06:12) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Comparison operators
Conditional Formatting
Excel formulas
Formatting cells
Highlight Open Items After 4 Days
ISBLANK function
Null text string
Order of operations
TODAY() function

Follow along with Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen in Episode #1717 as they work out differing solutions to this question.


and


"The Learn Excel from MrExcel Podcast Series"

[ensysconsultores]

Love the duel format, really enjoyed it. Keep it up.

[JUHILLAPSIWALA]

Amazing dueling Excel Gays, I have learnt 5% of excle from you and trying for more. You both my inspiration.
Thanks Excelisfun and Mr. Excle
And as Excelisfun say thanks Mr. Excel 🤝👏

[RaedAbdullahAlMuzail]

Thanks Bill for the bodcast Chandoo do a bodcast a bout how to find the last activity date from a list of activities but how we can calculate the duration between the activities and find the longest one?