HomeХобби и стильRelated VideosMore From: Rob Hambleton

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

880 ratings | 115501 views
Do you have an Expiration Date or Due Date field where you want to quickly spot past due, expired -- or soon to expire items? If so -- this is the tip for you!
Html code for embedding videos on your blog
Text Comments (59)
sojace (8 days ago)
Very good and clear to follow, just a question - I have to enter various dates in this format....2017/05.....or 2018/12, the year and month, I am really struggling trying to get excel to recognize this type of date format. Any ideas please?? Thanks
Rob Hambleton (7 days ago)
Put the date in, then RIGHT CLICK --> Format Cells --> Custom. In the box, build your custom date as yyyy/mm and it will change to that.
Thank you so much, you have saved my day!
Rob Hambleton (16 days ago)
Awesome!
Colin Wilson (1 month ago)
Thanks Rob, ideal for what I need however sometimes I have something when it should have a date I need Y or N for yes no or N/A! How do I do that for text
Rob Hambleton (1 month ago)
Do you want to format based on a Y or N or do you want it to show Y or N based on date logic. If the former, try conditional formatting --> Text that contains. If the latter, that's just basic if statement logic --> =if(cell is whatever date logic, "Y","N")
scottval321 (1 month ago)
Hi, is it possible once you set cell formatting to have each cell send email notifications to specified people?
Farah Mohammed (2 months ago)
Hi, has anyone tried doing this on a Macbook?
Keanu.Abenis (3 months ago)
Awesome! I have a question, Does this work in MS Excel 2007? Because it doesn't work on mine.
Rob Hambleton (16 days ago)
Yes. Exactly the same way.
JT (3 months ago)
Hope you are making money from this video...excellent! Thank you! thumbs up and subscribed!
Henry Quattlebaum (3 months ago)
No sound, thumbs t
Rose Leperlier (3 months ago)
I've tried many tutorials before I found yours! you saved my day Thanks a lot :)
kofi Gyapong (3 months ago)
thanks. very simple and professional approach
Christie Carter (3 months ago)
Rob, do you have any suggestions for formatting so that the dates are evaluated relative to the length of an associated task? For example, if a project task effort is scheduled to last for 90 days, flagging it orange 30 days out would be very reasonable, whereas using the same 30 day measure for a task that is scheduled to last for 360 days might not be sufficient.
Tayyab Butt (3 months ago)
Thanks, Rob, one question..If I have any date which is due after 7 days and it also depends on the word e.g if a status of anything is Alert its need to be done in 3 days and if it's significant it has to be added 7 days and so on... So how can I add both the alert status days added from the date of issue to get an expiry date condition? Thanks ...and yes BOOM SHAKALAKA
rivera82falcon (3 months ago)
Perfect! Exactly what I needed to know and how to do it.
Charmaine Tivis-Watts (4 months ago)
Hi Rob! This is really the most comprehensive video I could find! Thank you! I have a question: I am creating this exact kind of spreadsheet, however, if I have new employees (or cells) to add, how do I do this without manually doing the formula again to newly added cells? When I tried to apply it to the whole column, it made the whole thing red, including empty cells. Thank you for your time!
saad alqumer (4 months ago)
Thank you
David Bruno (4 months ago)
If someone wanted to use these to track upcoming, completed, and past due dates how would that work? I have three columns: "Due Date", "Start Date", and "Completion Date". If I apply icon sets in the "Due Date" column is there a way to check the "Completion Date" column to see if there's a value that meets or exceeds the "Due Date" column to flag accordingly?
Rob Hambleton (4 months ago)
They key with all of these is to build a relationship between today (the today function: =today()) and the due date. If you'd rather create the relationship between the completion date and the due date you could make the green parameter be due date (cell reference) >= completion date (cell reference). You could make the red condition be the other way around.
leorosa (4 months ago)
HI, in my database I'm given a date of placement and we have to deliver within 2 weeks of that date of placement. I've tried what you suggest, but seems it's not working. The difference I think is I have to equate the difference of date of placement to the current date. I've tried someting like if (today() - "cell" > 7,"1 week") it displays 1 week but I would also like to specify if greater than 14, then 2 weeks but can I input several conditions in one command?
Chris hedley (5 months ago)
Please help. I'm trying to use this to make a spreadsheet for skill currency dates......a task remains in date for 90 days on day of completion. So green for 80 days from today. Amber in 80 days from today and red at 90 days from today......how do you do this?
Matt Massage (5 months ago)
Amazing, Thank you !
Sophia Al Khawar (5 months ago)
Ok, i have a question here, now i add this condition using a TODAY, but each time i open the excel sheet to check which person in my department has crossed the due date, will it not take the TODAY from the computer when i open the file and change the whole formula??? how can we ensure it isnt move. Like i want an alert on when my colleagues have crossed the follow up date with a certain client! how will this formula work then?
Rob Hambleton (5 months ago)
Make sure you aren't just putting today in the conditional formatting parameter -- it has to be in there with the syntax of a function: =today(). That's the equal sign, the word today, and then a left parenthesis (bracket), and then a right parenthesis (bracket). Try that and let me know.
Kathleen Bias (5 months ago)
This is almost dead-on to what I'm trying to do. My only differences are these: Red=all dates before today; yellow=all dates this week; green=today; clear (or original formatting)=every date past the end of this week. The only question I have is what are the formulas for these conditions? Thanks bunches! You are really easy to understand, and I appreciate you!
Joel Angel Hernandez (5 months ago)
Hi, thanks for this awesome explanation. Quick question, I am using this format for a "bills payment checklist" so basically when the bill due date is 30+ dates away it is green, less than 30 days yellow, and past due red. I have also added a "checkbox" to click when the bill is paid. How can I make it reflect as green when I check the box, even though the date is past today? For example: right now, I have paid a bill for Oct 1st, but it is still showing red because it is Oct 6th, but it is not taking into account that the "Bill is Paid" box is checked. Hope I made this clear and that you are able to help, thank you so much for your time!
PADALA RAJ (5 months ago)
SUPER EXPLAINED THANK YOU
Sir, I saw the video and it was very helpful for me. But now i am stuck on a sorting of date. i done the highlighting of last month (red) this month (blue) and next month (green). i want to highlight the dates which are expired before the last month also. i cannot find a way. please help me on this. thank you
S (6 months ago)
This is far more simplistic than other tutorials, thank you
Saki Ayunan (6 months ago)
Thank you sir, indeed it is a big help previously I was struggling with this due dates now it simplified my work. Salut! God bless you and your family.
Arlyn Lim (6 months ago)
Thank you so much! This really helps! I've been looking for this for a week and finally found it! Thanks Rob :)
Idafuro Hartins (6 months ago)
thanks you so much this so helpful to me and my report
Bursayu (6 months ago)
Hi Rob, I'm trying to do a similar thing as in your video, but I'd like the colours to change based on the original date entered into each cell, so green if it's before and including the date entered, orange(amber), if it's within 7 dayes after and red, if it's 8 days and over. The problem I have is if I put a date in that's in the past, so if today is the 15th and I'd like to put a date requested as the 10th, the colour should already be changed to orange(amber), but because it's in the past, it doesn't change (frustrated face). The whole thing is based around of a document requested - document received, so a colour of the cell changes, as time goes by. I hope I've explained it properly. Cheers Sasha
Bursayu (5 months ago)
Rob Hambleton  Hi Rob, Sorry to be a pain, but is there any chance you could share the formula with me, please? I've got =today()+6 for the first 6 days in one colour, then =today()+14 in a different colour and so on, but it doesn't change colours as the time goes by, which in need it to, in order to warn me that I've been waiting for a document too long. Cheers Sasha
Rob Hambleton (5 months ago)
Hey Bursayu! The conditional formatting over the date field will use the today function relative to those dates, whatever they are, to indicate red, yellow or green. That's the point. The current date relative to the due or expiration date is what is being formatted. Does that make sense?
royeden medeira (7 months ago)
How can we add parameters...Like different items that have different expiration dates...??
Rob Hambleton (6 months ago)
Once you set the parameters up, you can just add lines with different expiration dates and the conditional formatting will work. Also, if you want to have different date window parameters (instead of the 30 days, etc.) you can make that a variable and then a cell reference in the calculation. For example, instead of =today()+30 as the parameter it could be =today()+ cell reference (one for each line if you'd like) and you can make that a variable as well.
mahamohan1 (7 months ago)
Nice way to set reminders in excel
Octavia Queen (7 months ago)
I have been looking for this for MONTHS!!! where have you been???lol! Is there a way to create a bills spreadsheet with due dates for the year... i have great ideas, just not sure how to implement them. I appreciate any help.
Kumaresamani Karuppasamy (8 months ago)
Thank you so much for your explanation...Will try and write you.
Vivek Anandan (8 months ago)
Hi Robert It was an excellent narration in an understandable AV explanation. Thank you so much. I personally had great feeling you as a mentor.
Rob Hambleton (8 months ago)
Thank you Vivek!
Cindy Ceroky (8 months ago)
Hi Rob!! What an excellent tutorial, thank you so much! Question: on alerts, once it has changed to a color, can you change it to clear if the task is completed? My scenario is that I need to make patient alerts for over 7 days, then over 30 days, then over 90 days...for different status related to that one original date. Would you suggest I just make 3 separate columns for that, then once the item is completed, place "n/a" in that column or something? trying to keep it simple but the tasks themselves are exactly why i'm making the spreadsheet!!! it's a high volume until I can chop it down.
Rob Hambleton (8 months ago)
I would create a status field in the data base, then use DATA VALIDATION to set up whatever status levels you want to use, you could drive conditional formatting (text that contains) with the different words -- PAST DUE (date driven or manual selected) would be red etc. Does that make sense?
Shaukat Samad (10 months ago)
Great, the teaching of yr style is excellent, and very simple but unfortunately the color the yellow one is now working, I have tried several time but couldn't work any rest of colors was perfect....... thnks for teaching us.....
Rob Hambleton (10 months ago)
When you set this up, because you are using the today() function, you need to make sure that the dates you use coincide the the thresholds you are using. Try a date earlier than today, a date less than 30 days from now, and a date more than 30 days from now if you are going to use the 30 day window. Then, simply layer the less than, between and greater than conditions as I've explained them across the range of dates. It should work.
Prabhakaran Gopal (10 months ago)
Thank your very much for your Great Tip
Asim Jamal (11 months ago)
Great Tip, Awesome Work!
Hami Salih (11 months ago)
you explained in a very professional way. Thanks, dear.
Alex Vidu (4 months ago)
Cheers for the video content! Apologies for the intrusion, I would appreciate your opinion. Have you tried - Weydaniel Organized Dominator (probably on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the normal expense. Ive heard some extraordinary things about it and my work colleague at last got amazing success with it.
ilcalabrese88 (11 months ago)
Hi! How would you make this formula work? If A1<=toda() then A1=today (), so that a date can't became older then today
Rob Hambleton (11 months ago)
These icon set parameters are actually different formulas. You can layer multiple conditions in a single range. Just give it a try using the exact steps in the video and you will see they work.
Rob Hambleton (11 months ago)
The video shows you how the formula works. The parameters are different formulas built into the icon set rule. The video shows you how to set it up.
Tanya Lee (11 months ago)
This is amazing thankyou for explaining this for us technologically challenged individuals.
Ahmad Odeh (11 months ago)
Excellent..simple and clear .. good job
Juan Carlos (11 months ago)
Thanks a lot Rob, best regards.
Gigi Bliss (11 months ago)
I have been trying to figure this out for months! (I'm Excel challenged) thank you so much!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.