Excel Essentials -- Level UP! -- Conditional Formatting for Due Dates and Expiration Dates

[Music] well hello and boom-shaka-laka you know a question I get asked a lot when I'm teaching a live Excel class is there a way to show in a date field when something is either expired or past due where something is becoming getting close to an expiration date or a due date or when something is comfortably not even close to being expired or due so there is a great way to use a feature in Excel called conditional formatting to do that for you and I just wanted to show you two quick ways to do it here in Excel so what I have is I have two columns here I've just labeled one expiration date and another one due date but they're the exact same dates and the parameters I'd like to set up are that if today is if the expiration date is expired that is earlier than today then I want to initially shade the expiration date field with a red if it's within 30 days of expiring I want to shade it with a yellow and if it's beyond 30 days or if the expiration date is 30 days or longer in the future I just want to go ahead and shade that with green so we're gonna do that first so when I highlight this group of cells here now we could highlight an entire column so that you wouldn't have to do this or expand the range whenever you needed to do it but I'm just going to stick with doing these three cells right here so I'll go ahead and highlight those I'll go up to the conditional formatting button I'm going to drop down here to highlight cell rules and I'm gonna go ahead and pick the less than condition for my first condition so I want to say that I want to format cells that are less than equals today left paren right paren and I'm going to highlight those as being expired so I'm going to use light red fill and dark red text so that condition is done now we need to do our yellow condition so with the same range we're gonna go layer in a second condition we're going to go to highlight cell rules and we're going to do the between Dean condition we want to format cells that are between equals today and the upper parameter is going to be equals today plus 30 we're going to highlight those cells as yellow phil and dark yellow text so those would be the ones that are within 30 days of expiring and finally we're going to go to conditional formatting and we're going to pick a greater than condition and we're going to say we want to format cells that are greater than equals today plus 30 with green fill and dark green text and so there you have it now anything that's expired that is where the expiration date is before today then it it shows up in red if it's between today and 30 days out it's yellow and if it's beyond 30 days out it's green now the cool thing about this is because we have the today function built into the conditional format this will adjust to whatever day it happens to be relative to your expiration date so you don't have to put it put a fixed date in there the today function will always incorporate today's date according to your computer into that now I think a more elegant way to do this is to use conditional formatting feature called icon sets let's do the same thing but well let's do with icon sets right here in this range so if we go up to conditional formatting and we'll drop down here to icon sets and we'll pick this green yellow red icon set right here now what we'll do is having put the icon set it's not what we need yet we need to adjust it so we're gonna go into conditional formatting with this range highlighted and we're gonna drop down here to where it says manage rules the only rule we have in place for that particular set of cells is icon set rule so I'm gonna select it and then I'm going to click Edit now right over here this is the typical boilerplate which really has no Apple occasion of what we're trying to do here with icon set so we're immediately going to change the parameters to formula based parameters for both of these conditions now with a three icon set rule all you have to do is set two of the parameters and the third one is implied so the green parameter is going to be equals today plus 30 and then the bottom parameter is going to simply be equals today and let me interpret that for you so what that will say is the yellow condition will be anything between today and today plus 30 and the red condition will it be anything less than today so you don't have to create that third parameter it's implied by putting those two parameters in there so here's how you do it with icon sets then check it out we're going to go ahead and click OK and then ok and here you go so you decide which one you like better this is the traditional way of doing conditional formatting what she'll see with cell shading and fill and that kind of thing and this is the icon driven way so there you go play this over try it you can put it in an entire column where you have dates where you want to have your attention focused on dates particular items that may have been expired or getting close to expiration this can be used in project planning it can be used when you have different types of things that in the food services industry where you want to be tracking expiration dates in all sorts of places now if you have any questions at all about this please feel free to give me a call or contact me my name is Rob Hamilton we're from Sol Canyon training and development also I'd like to just let you generally be aware that we have a wonderful program out there called Excel essentials that has over 50 tutorials where you can basically learn excel at your own speed if you'd like to learn more about that go onto our website wwlp.com click on over to products will tell you all about it once again if you have any questions at all let me know so I wanted to share this one you with you I get this question all the time and I'm finally putting out a tip that gives you an easy way to track expiration dates and due dates boom Shakalaka [Music] you [Music]

Channel: Rob Hambleton

Views: 594,693

Rating: 4.859725 out of 5

Keywords: excel, tips, tricks, conditional formatting, formatting, dates, expiration dates, due dates, techniques, excel tips, power, power tips, shortcuts, excel skills

Id: SIhBMvuoZNE

Length: 6min 53sec (413 seconds)

Published: Tue Feb 20 2018