Скачать или смотреть Excel - How To Highlight An Entire Row Based On Date In Excel - Episode 1906

Microsoft Excel Tutorial: Use Excel Conditional Formatting to Highlight Entire Row if Date is 60 Days Past Due

Welcome to the MrExcel podcast. In this episode, we will be discussing how to highlight an entire row in Excel if the date is over 60 days old. This is a common issue faced by many Excel users, and we have a simple solution for you.

The question for today's episode comes from Ross in California. He has a date in his spreadsheet, let's say June 11th, 2014, and he needs to highlight the entire row when it reaches 60 days past that date. This may seem like a daunting task, but with the right formula and conditional formatting, it can be easily achieved.

Before we dive into the solution, let's first talk about the =TODAY() function. This function is a great tool to have in your Excel arsenal as it gives you the current date without the time portion. This means that it will automatically update every day, making it perfect for our scenario. Now, let's get into the solution. We will be using the conditional formatting feature to highlight the rows that are more than 60 days past the date in column C. Simply select all the data, go to Conditional Formatting, and choose "Less Than" as we are looking for dates that are more than 60 days old. Then, enter the formula =TODAY()-60 and choose your desired formatting. This will highlight all the rows that are older than June 11th in our example.

But what if we want to highlight the entire row instead of just the date? This is where it gets a little trickier. We need to figure out which row is the first row in our dataset (in this case, row 2) and which column has the dates (in this case, column C). Then, we select all the data and go to Conditional Formatting, New Rule, and choose "Use a formula to determine which cells to format". Here, we enter the formula =C2＜(TODAY()-60) and choose our desired formatting. This will highlight the entire row if the date in column C is more than 60 days old. Just remember to lock down the column reference with a dollar sign to ensure the formula works for all the cells in the dataset.

In conclusion, highlighting an entire row in Excel if the date is over 60 days old is a simple task with the right formula and conditional formatting. You can easily customize this solution for different time frames by changing the formula. Just remember to lock down the column reference and you're good to go. Thank you for tuning in to this episode of the MrExcel podcast, and we'll see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents:
(00:00) Highlighting Rows Based on Date
(00:24) Introduction to the TODAY function
(00:56) Highlighting Entire Rows
(01:45) Selecting Data and Creating Conditional Formatting Rule
(02:07) Using a Formula to Determine Formatting
(02:17) Writing the Formula
(02:43) Locking Down Column Reference
(03:08) Customizing Formatting Options
(03:46) Applying the Rule and Viewing Results
(04:06) Importance of Locking Down Column Reference
(04:20) 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 #exceldates #excelsequence #conditionalformatting #conditionalformat

This video answers these common search terms:
how to highlight an entire row based on date in excel
how to conditionally highlight an entire row in excel
how to highlight entire row in excel conditional formatting
how to highlight entire row in excel using conditional formatting
how to highlight entire row in excel with conditional formatting?
how to highlight entire row excel conditional
how to highlight entire row with conditions excel
how to highlight an entire row on excel
way to highlight an entire row in excel
how to make an entire row highlight in excel

Ross has a data set in Excel. He wants to highlight the entire row if the date is more than 60 days old. Today's episode shows how easy it would be to highlight the date cell and the formula necessary to highlight the entire row using Conditional Formatting.

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...