Highlight Overdue and Expiring Business Days | Conditional formatting in Excel | Excel Tutorial

thanks for tuning in to this video it'll be short but it's super packed with extremely valuable information so make sure you tune in to the end first i'll touch on highlighting dates that are either due today or overdue then i'll touch on dates that will be expiring soon i'll also cover highlighting due dates or expiration dates while factoring in business days or in other words excluding weekends and holidays as always we have a ton to cover so let's dig in all right so i'm inside of microsoft excel as you can see i have two columns in my data set this is a pretend set of workflow case numbers that my imaginary team needs to get done before i send these to my team to complete i want to highlight the most urgent cases in red in my line of work we have the goal of completing the cases within 5 days after we receive them so in column b it shows the date that we receive the case number listed in column a i need to highlight the cases that are 5 days old or older to show more urgency i'm going to show you how to do that in two simple steps i'll show you how the today function works in microsoft excel then i'll show you how to use that formula to set up conditional formatting to highlight items that are more urgent in cell e1 i'm going to show you the today formula and it's simple all i do is type the equal sign and then the word today followed by two parentheses then when i hit enter it will show today's date and cell e1 i am filming this on october 30th 2020 just so you know why it's not showing today's actual date pretty cool right but let's say i want to know what the date was five days ago that's very simple as well all i need to do is add a negative five or a minus five to the end of the formula which is telling excel to subtract 5 days from today the cool part about this formula is that it will always show what today's date is if i close this excel sheet and open it again tomorrow it will show tomorrow's date as today it updates automatically alright so now that you know how the today formula works in excel let's put that to use within conditional formatting i want excel to highlight any date that is older than five days from today i'm going to highlight all of column b to tell excel that what i'm about to do with conditional formatting will be applied to all of column b then i'll click the drop down for conditional formatting which is located in the ribbon on the home tab from there i'll hover over highlight cell rules and then choose the less than rule you should get a pop-up like this that will likely just have a random date in there i need to adjust this rule a bit so instead of highlighting any cells that are less than october 27th i need to tell excel to highlight any days that are five days older than today so i'll replace that with equals today opening parentheses and closing parentheses then i'll add a minus five i'm going to choose to keep the light red fill with dark red text for this one now i can just click ok from here you could add another rule to highlight anything that is less than three days old and yellow is done the same way now i'll just type equals today opening parentheses and closing parenthesis and then minus three and click ok now you might notice it replaced all of your dates that are older than five days which were previously read that's a simple fix just ensure all of column b is highlighted and then choose manage rules from the conditional formatting drop down now we just need to tell excel what order to add these rules if i click on the older than five days old and then move it up in the order we should be good when we click apply now you might be thinking this is all fine and dandy but what if i need to highlight dates that are going to expire in the future for instance let's say that instead of the date in column b showing when you receive the case it shows the date that it needs to be done by let's cover that next it's a very simple process in this example i have dates in column b that are all in the future remember the date when filming this is october 30th 2020. i want to highlight red any dates that are in the next two days because those are the ones i need to get done first i'll do that by highlighting all of column b and then from the conditional formatting drop down i'm going to hover over highlight cell rules again and choose the less than option again in the formula bar in the pop-up window i'm going to type equals today opening parentheses and closing parentheses and then a plus sign and then the number two then i'll click ok now all the dates that are less than two days from now are highlighted in red i can add in yellow to show any dates that are less than five days from now the same way i'll highlight all of column b and then choose less than from the conditional formatting drop down then i'll type equals today opening parenthesis and closing parenthesis followed by plus five then i'll choose yellow fill with dark yellow text and hit ok now like earlier i need to tell excel what order to put my rules so i'll go to manage rules and move the less than two days away up in the order now i do want to show you one other step that some of you are probably asking for at this point there are probably some of you that aren't working seven days a week you need to get items done say within five business days instead of just plain five days let's go over that now in this example i have dates that are up to 10 days in the future i want to highlight any dates that are within one business day of today this will be done a bit different first we need to understand how the workday formula in excel works in cell e1 i've got the today formula which displays today's date in column h i have a list of us holidays because my team does not work on those holidays in cell f1 i want to find out what the date will be in five business days from now that's where the workday formula comes in i'm going to start by typing equals workday and then opening parentheses the first thing excel asks for is the start date in this case it's going to be today so i'm going to choose cell e1 i'm choosing e1 because e1 is always going to show today's date now when i add a comma the next thing excel asks is how many work days out do you want to look at in this case i want to look at 5 business days out so i'll add a 5 and a comma to get to the next part of the formula now it's asking for a list of holidays that you want to exclude from the working days as well i'm going to highlight my range that has all the holidays listed so i'll highlight from cell h2 to h9 now i'll add a closing parenthesis and hit enter this is telling me that five business days from now is november 6. let's look at a calendar to see if that's correct so in this case we'd skip saturday and sunday then we'd count one two three four five looks like that's correct now since we know that november 11th is a holiday let's do 10 business days out instead of 5 to test that part out as well i'll do that by changing the 5 in my formula to a 10. let's look at a calendar now to see if it skips november 11th so we skip saturday and sunday again then we count one two three four five and then we skip saturday and sunday then we count six seven and skip the eleventh since it's a holiday then we count eight nine and skip saturday and sunday this means that november 16th is in fact the 10 working business days now let's work on highlighting any date in column b that is less than 5 business days from now the first thing i need to do is change the 10 back to a 5 in my formula now i'll highlight column b and choose the less than rule from the conditional formatting drop-down again so i haven't been able to figure out how to build the workday formula into conditional formatting so i'm just going to choose f1 to be the parameters of the less than rule now this rule says that we want to highlight any date in column b that is less than cell f1 now i can just click ok now any date that is within the next five business days is highlighted red those are the ones that my team needs to get done first i hope you learned something from this video if you did can you do something for me real quick to help out the youtube algorithm click the like button and leave a comment both of those will help this video get in front of more people which will in turn help my channel also if you haven't done so already be sure to subscribe to the channel and click the bell notification doing so will notify you anytime new videos come out on my channel also feel free to browse the videos i think a few should be popping up right now see you next week

Channel: Work Smarter Not Harder

Views: 2,312

Rating: 5 out of 5

Keywords: Microsoft Excel Tutorial, Due Dates in Excel, Highlight Due Dates, Work Smarter Not Harder, Expiration Dates, Urgent Dates, Highlight Urgent Dates in Excel, Excel, Microsoft Excel, conditional formatting in excel

Id: H1bEpd-i4DM

Length: 7min 58sec (478 seconds)

Published: Tue Nov 03 2020