Excel Essentials -- Level UP! -- Conditional Formatting for Due Dates and Expiration Dates
HTML-код
- Опубликовано: 29 сен 2024
- 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!
How?? How is a guy from 6 years ago who says "Boom shaka laka" more helpful than google, GPT-4, my computer science honors teaacher, and the rest of youtube combined??
Rob, BOOM SHAKALAKA! Thank you for this! You made it SIMPLE and engaging. Will be taking the course suggested. Way to go! :)
I'm glad it helped. Thank you for taking the time to give feedback!
Thank you Rob, very easy to understand
Loved the icon tip
Excellent! This is great and thanks for sharing.
you had me at, "boom shakalaka" !!!!
Thank you so so much!
You are welcome! Glad it helped.
What if i dont want to day it for today but like 6 months ago what would i use to replace today
The conditions run at different windows of time with today being the anchor. So -- 6 months ago would be today() -183 or so. So, if the date in the cell is less than (
Great video Rob, thank you!! I have a question, I would like the yellow to reflect a warning that a certification will be expiring in the next 30 day. For example, say today's date is Jan 24, 2019, a certificate expires on Jan 30, 2019. I would like the yellow to indicate from Jan 1, 2019 that it is going to expire in the next 30 days. This seems pretty simple but it's not working for me. Thanks for your help
If you want to try answer your question in google sheets:
Reminder System | Google Sheets | Conditional Formatting | Apps Script | Email Notification
ruclips.net/video/UZ9112PoBMQ/видео.html
Does this update every day u open the file ?
Yes -- the =today() function updates every day/time you open the file or whenever you hit recalculate.
@@soulcanyon thanks Rob, Rob. 👌
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
The way I would handle this is to set the due date with the IF function driven by the word "Alert" and "Significant". So, =if(cell reference = "Alert",start date cell reference + 3,start date cell reference + 7) -- something like that. From there you can conditional format relative to today.
what if i want to use a different date than "today" ?
To do what? The purpose of this application is to assess a gap between a due date or an expiration date and today's date to spur action, followup, etc. What's your application?
This video could be 5 years old, but my man has given me faster the answer than ChatGPT and Bard together (about 1 hour trying these AI ideas, that obviously didn't work) great work Rob, cheers
exactly the same, I knew the moment the video started with the audio being messed that this holds the answer, you never find what your looking for in a flashy video
My right ear is lonely.
Good catch. I've since fixed that audio mixing glitch.
It's always an old video from the internet that really helps you 😂
I am trying to do this same thing but with an additional parameter. I keep having one parameter override the other. For instance, I have the cells turning yellow when the project is due within 7 days, and orange when it is due within 3 days. The yellow only shows up, even when i am within 3 days of the due date. Is there any advice for this issue?
In the demo there is a "between" condition. That is your yellow condition -- between 3 and 7. Orange is 3 AND
This tutorial is perfect! It saved me SO much time!! It's easy to understand and apply!!! THANK YOU!!!! I'm subscribing!!!!!
This video was awesome! From here anyway i can populate a total column of items not expired?
So so so helpful.. thank you so much
because of the way the audio is on this video i thought my headphones were broken cuz the audio was coming out of my left hear XDD
Yes -- there was a mono --> stereo mix issue on this video when I originally posted. It will be fixed in a future post. Thanks for noticing and watching.
@@soulcanyon❤❤❤❤❤
I'm pretty sure I've come back to this at least once a week hahaha... 🤣
Hello, I have a list of product, those have different different expire dates. I want my product status cell will be red color after a certain product has expired. Which formula should i use in conditional formatting?
the formula, or condition, for the conditional format would be
From the days of "boom shaka laka" to current day - the rest of this video is still very relevant. Thank you. BTW -- I hope you're not still saying "boom shaka laka"
Thank you Rob, I was searching for this to assist my daughter with a work spread sheet and you explained it so clearly. Indeed it is a big help and time saver for my daughter who is assisting people and does not want them to fall behind. This will simplify her work. God bless you and your family!
After 6 yrs of making this video I still find it helpful. Thank you so much.
Great to hear it!
So how do you format the cells to automatically shade 2 years after the date that is entered?
You'll want to make the conditional formatting formatting formula condition key two years (just use a formula against the cell reference that contains the date and add 730 to it (2X365) -- close enough, right? So, the condition for that shading would be >= cell reference (e.g. B4) + 730, because a date is just a number sequenced by day with the numbering system starting on January 1, 1900. Try that and let me know.
How can you have it send a reminder in addition to having the worksheet prompt you xx days out w/color codes and/or icons?
I've been asked that question may times and don't have an answer. Sorry.
It doesn't work on my excel. Think my condition all formating doesn't work at all
Hey there. Conditional formatting is powerful, but it's extremely syntax driven -- that is you have to make sure that the conditions follow the proper structure, especially the first type demonstrated in the video. The today function, for example either has to be written as =today(), or within the conditional formatting today() -- you have to have the parentheses. Go back and watch it again and try to set up exactly what I've done in the video and I think you'll eventually get it to work. Good luck.
Thank you so much! Doing a deadline list for my job and the web pages on how to do this are such jargon or only cover 1 formatting rule. This showed me perfectly how to set this up
Awesome. Thanks for taking the time to comment.
Super useful video. Have set up a list of domains so it's nice and easy to see when they're expiring.
This one rocks...thank you so much...subscribing from now on :)
Awesome. Thanks for the feedback!
What would be the formula to capture and count all days list in a given month.
All days that fall within a condition? If so, =countif(range that includes the dates,month(date) = condition) would be the basic layout.
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!
Try this
Convert your data in to table, den apply conditional formatting it will work.
Hope I answered ur question.😁
MM is correct, if you convert the spreadsheet to a table then when you insert or extend the database the formatting will be added automatically.
You can create the table structure in your database by either HOME --> Format as a Table or INSERT --> Table
Hello Rob i have one question : I would to keep count of the red, green, and yellow cells. Which formula can i use
Hey Lawrence -- I don't know of a way to do that directly with conditional formatting. However, since conditional formatting contains "if" logic, you could build the same if logic into the countif or countifs function to get the counts you are looking for.
How can you make this for year due dates for example annual trainings is due a year from completion so have it go red for the over due and yellow when its 30 days before the due date
So, if I understand you correctly, you have a training date and then you add 1 year to it (time when training refresher needs to happen)? So, if a training date is in cell a1 you could then have another field that would be training valid until date, or something like that. You could have a formula in that cell that would reference a1 and do this: =date(year(c1)+1,month(c1),day(c1)). This will give you a day 1 year exactly from the training day, and then use the instructions in the video to do yellow fill formatting on the cell with this condition: =c1
Thank you!! do you have any videos on doing this? My entire office looked for one on youtube and weren't unable to find one
This video is worth a million dollar, best video ever, straight to the point. thanks a lot
Thank you! I'm happy it helped!
I have a set of dates and it won't highlight. I tried creating a new date and it worked. How do I format my current dates from a pre-existing data so the conditional formatting will work?
The two main reasons this won't work are: 1) The date is not in one of the condition ranges and/or: 2) The "date" is not really formatted as a valid date. One way to check this is to change the format on one of the suspect cells to number and see if it changes to a number. The number will actually be the date value. If it doesn't change to a number it's not a valid date and is simply text. If you want to convert this data to valid dates there are a couple of ways to do it using the month, day and year functions inside of the date function on the concatenated text. If you don't want to do that, you'll just need to manually reinput the dates as valid dates.
A quick clarification. If you do data==> text to columns on the invalid date value date and designate the delimiter as whatever is separating the month,day,year and THEN use the date function to point at each respective piece to reassemble the date, you'll be fine. Actually really easy to do.
Hi Rob - Is there a way to conditional format the entire row based on information in one column?
For example, I have a sheet contains a list of events with the date info in one column. Then I conditional format the date column with "=today()" so that all dates passing today will be highlighted. But how do I make sure the entire row, not just the date gets highlighted, is there a way to do this?
Hey Leslie. Yes, in order to highlight an entire row based on the value in a field, click the conditional formatting button, new rule, then use the formula option for conditional formatting. The formula will reference the cell you want to set the condition around, then you can set the formatting that goes with that condition. The formula will automatically absolute reference the cell, so I remove the row absolute reference ($) before format copying the condition to the remainder of the sheet. You can also prehighlight the entire sheet, the the condition should automatically relative reference.
Instead of 30 days out, how would you use this function for 6 months?
There are lots of different ways of breaking the date apart, adding 6 to the months portion of the date then reassembling it using the DATE function. However, I would probably just change the 30 to 180, or 182 and call it good.
This is so easy to follow, every other one taught this in the most difficult way possible.
Thank you Yissy! I'm glad it helped.
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!
Hi Rob, please help?... I have a spreadsheet with a column for expiration date...I want this sheet to generate and send email reminders automatically by 7 and 3 days prior, also to send another email if past due by 3 and 7 days, is this something could be done with formulas or VBA? Thanks much
Rob i need help with color formatting cells with OPEN CLOSED and On Going. I tried everything and I can't get it to comply
I will like to do the same
In traditional conditional formatting --> Home --> Conditional Formatting --> Highlight Cell Rules there is a rule for Text that Contains. You'll need to set up three rules for the range/field that includes CLOSED, OPEN and ON GOING. On the left side of the dialogue box for each rule you will put the text, on the right side you will select the type of format you want to go with each rule.
nice tutorial this is very useful
How do I make a chart to show the percentage of items that are green, yellow or red on a rolling calendar?? Someone please comment a link! Thanks!
Audio only coming through on left ear. Problem specific to this video
That was a quirky rendering issue. You are the only one to mention it -- but I will be re-rendering it with mono selected so it comes over both channels. Thank you for reminding me!
Hello
I need your help with that formatting. so I need to make a range but only for months not days. what I mean is if have a range of 5 months from March to July for Ex, I want the excel to highlight April, May, and June in green and if it is less than May, or more than June to be in red. but please notice that when I work I work after the 3 months so I need a dynamic rule for the month that says for Ex if it is less than 3 month of the current month.
Thanks in advance
They key here is to put the TODAY() function inside of the MONTH() function. For example, Month(today()) will give you the month number of whatever month it is. Then you can do your formula or icon set logic formula around +/- the month number. Try that and let me know how it goes.
"Boom, Drop the Mic"!!! Thank you Rob! I am a CTE teacher and my students work in a Lab style setting. I have five (5) courses going on at the same time and since I am all over the place in the classroom; this video for my Excel students has been a 'Blessing' in this chapter of their book! Thank you for the easy walk through. One thing I have learned through my walk is that: "If you don't use it, you lose it"!!! I will try to follow your videos and maybe your Essential courses!!👍 Again, Thank you to the moon and back!😃
Hey Norma -- Thanks for taking the time to post and I'm happy the video helped. Keep up the good work!
Hi, I have been looking for something similar but with text columns and I was hoping you'd help me since your videos are very well explained. I have a date column and an "Approved Y/N" column. What I want to do is highlight the approved column only if it's approval is still pending after 10 days. All I've been able to find is date-to-date comparisons and "will be overdue" so it doesn't help me at all and my Excel skills are barely past beginner.
Rob,
Your tutorials are mind blowing - yet so easily explained.
I hope you can help me solve my little problem.
If I want to know that which month and year will come after 591 days from today - is there any formula in excel which will show it?
e.g if today is 13.11.2022
- after 623 days it will be 28th July 2024 (Sunday).
My mobile app can do that.... but can excel tell me that?
In that format?
even 28.07.2024 will do.
If yes - Please tell me - how?
Thanks in advance.
Format the cell with the formula in it to whatever date format you want. Formula is then =today()+591. If you want the exact date format shown in your question, you can set up a custom format of dd.mm.yyyy
And thank you for the compliment
@@soulcanyon Sir, You are a life saver.
You replied so promptly and gave me exactly what was needed. So simple and so nicely explained.
Thanks a million.
@@rjdr6014 You bet! Glad I could help.
How is it possible to do conditional formatting from the date within the column rather than today's date? For example I have a raw material delivery date on 01/12/23 in column B and the expiry date on 12/09/23 in column C. How do I format that condition to show any raw material that the expiry date is less than 6 months from the delivery date is highlighted? All conditional formatting I have seen needs to be either today's date or a specific date which will need to be manually inputted everything....help!
This does not work for me. I used the B2
I need to organize a bunch of committees at work (mainly term expirations) on an Excel sheet. It takes us a year to find/approve new members for committees. If I want to be alerted that a committee member’s term is going to be expiring in the coming year, is there a way to do that?
How would I do this if i have an issued date vs expired date. I know I can easily convert the date, but i'd have to make a separate column which i'd prefer not to make. I am creating a log of training records and the dates in which their certificates were issued. Example, training was completed on November 12, 2019 and it expires in 2 years. I'd like to get a 60 (yellow) and an expired notice (red).
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!
If you do the entire column how do you keep it from filling in spots where there's no data yet?
You can add a first condition that has no formatting if the cell is blank (""). With multiple conditions for a cell, if there are conditions in conflict it uses the top most (latest) condition.
@@soulcanyon thanks!
Watched a lot of videos and wasn’t able to get what I want. But FINALLY! an easy step by step video and exactly to the point! Thanks a lot man! :)
Thank you so much for teaching this easy step. God bless you.
Thank You so much for this video! I knew there was a way to do this and this was the only video I found to really lay out how to apply it simply to individual or groups of cells. This will help me so much with organizing my maintenance schedules!!
Awesome! Thanks for taking the time to comment.
Hi i have a question to highlight red expiration for the whole month example today is feb.7, i want all itrms expiring the month of february to be red not only t0day. What is the formula?
it didn't work with me
It works. Keep trying.
There are so many moving parts with conditional formatting -- including overlapping conditions, ranges, formatting options etc. -- just keep reviewing the video -- pause it and keep trying. You'll get it.
So I was wondering after the cell conditionng is it then possible to have a colum next to the dates stating in text "overdue, high priority, low priority"? I would then have two colmbs, one with the highlighted dates and the one next to them saying "overdue, high priority ect" if that makes sense?
Hi Rob, I have a situation that I need to highlight over 30 days, and over 60 days from a date of service. Can you tell me how I can do that? This video comes really close to it, but not quite.
I’ve been looking all over I want your layout but image an entire column of dates under A.
Basically I want to assign the entire column with specific dates to look for but not based off of today but based off of the date in the column
How do I avoid the rest of the cells in the column from going light red if a date hasn't been input yet
With traditional conditional formatting you can later a blank ("") logical test condition in with no formatting.
Hlw Rob, How r you?
I need your help. Will u help me to make an excel sheet for master list of SOP (Standard Operating Procedure). I want to make a sheet which will notify me before 20days of ending review date.
Hey Imran. If what you mean by notification is have the sheet format the record or a cell when today gets within 20 days of the ending review date, then for the range that has the ending review dates in it the condition would be: (the cell that has the date in it is c4, for example) -- =($c4-today())
Imran, that is exactly what I am trying to do. Did you manage to accomplish the task and if so, could you share please?
Hi can you please teach how to create a periodical tracker, for tasks which needs to be done weekly, monthly, quarterly...? please thank you
If I need to monitor expiry dates for 3yrs & 5yrs, does the formula change from +30 to +1095 (3yrs)?
how does this work with previous dates instead of future dates
Same way. If your date is in the past you can set up logical operator parameters between those dates and today or fixed dates in the future. This all hangs together by understanding that one day in Excel is valued as 1 -- so when you are subtracting one date from another date, or adding a number of days to a given date you will be getting a subsequent date that is that many days different -- negative (past) or positive (future)
I have to generate column data from 1 to 1000 and after 1000, I want to repeat 1 to 1000 again below the previous values. can you help out?
You can do that with an if statement that refers to the cell above: =if(cell reference
HI Rob . hope you are doing great... I just want to know how to manage formula for dates in cell. i have two cell, one is plan and one is actual. How i determine that if the plan date is
Is there a way to insert audible alarm or voice message as reminder?. it would help if it Can automatically change color as per approaching date, due date, expire on date and expired date !
hi Rob. good vid. i was wondering can this be applied to a variance budget, ie a range of % over budget =excess (in red) and if less then surplus(in green) and if no change (amber).. how would i do this? a video on it woud be really helpful or an exlantion thanks
Absolutely. variances are probably the most common application of conditional formatting! Good luck!
Pls sir, I want to send an excel workbook to someone in an editable mode but I don't want someone else to copy it from them for personal use. Thank you very much
I am looking for a way of conditional formatting to show a date occurs before today. I have a column of dates with different months. I need to be able to high light something that is past due.
Hi, this was super smooth to follow but nothing happened when I put the information in. Any thoughts what might be the issue? Tia!
Instead of days is it possible change to months?
Sure. You can do that in a number of ways. You can use the approach in the video to key off the first day of a particular month -- say 11/1/2021 for example OR you can create another field and use the month function on the due date or expiration date and then format based on that. The approach is the same either way.
Fucking amazing, thank you.
Boom rob! This work is great! Can this be done vba? I was looking for this vba codes in time day values!
Many thanks. I nedded this
any chance you still working Rob need a hand with yearly updates. My excel wont let me complete it. thanks Jon
thanks rob I am from Malaysia. I am searching this method at 4 a.m to manage my task. this helps me a lot, especially In the food industry.
Awesome. Glad it helped Khalid!
I love you!!!!!! Thank you. Made me the shiznit at my job.
Awesome! I love hearing that.
What if I wanted the icons to be on an empty cell on a different sheet? I’m wanting to use this for software license tracking. My hope it I can have a summary sheet with a list of computer names and the icons next to them based on the dates of a different sheet. If that makes sense.
Just played with this a little. Definitely can't find a way to do it with icon sets BUT with traditional conditional formatting (the first part of the video) you should be able to create a formula to reference that cell and do the formatting off of that reference.
Rob Hambleton that’s what I kind of ended up doing. I referenced each cell individually. What sucked is that for some reason the blank cells copied the date of 1/1/1900 instead of leaving them blank. It worked well though. I had the highlighted rule on the original sheets and the icons on the summary.
@@newportburn If you are calculating a date to put the conditional formatting on, but the base for the calculation isn't there yet, wrap the if function around the calculation like this: =if(cell reference ="","",calculation). That will leave it blank until there is something to calculate.
Can I use the same format with a condition in which a separate column has date if date is not there it will highlight but if date is the it will not. Example: I have data for vehicle fitness certificates along with other data such as vendor date of vehicle . In vendor column I update dates of payments made. What I want is if the column coresponding with the date is empty then condition 2 of the video subject will function. If date is there ignore.
That particular condition is a formula referencing a cell with a blank, or the cell itself is blank. The logical test operator for that is "" (two quotation marks). That would be your logic for the condition. Try that and let me know.
Awesome! Perfectly explained. One question; how to link this to different tasks or qualifications (selected from a Dropdown-Menu) that might expire?
If a due date or expiration date, qualification date is linked to what is selected in the drop down, then same way.
Great video. The icons are not showing when I use the icon sets. Do I need to turn these on? If so how? Thanks again for your help
Hi bro, i have one doubt can u clear?? How to hide the formula using condition formatting??? Or how to give the values in certain condition?? Ex. If i type column C as same day then rate will be appear automatic in column D. How to set???
Hi Rob, whats your website link?
www.soulcanyon.com
So helpful - thank you!!
Hi! Is there a way to automatically apply the conditional formatting to additional rows that are to be added?
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.
Thank you, am going to use this method. I have been looking for something like this to use on my debtor's age analysis. I want to build a system that would be able to notify me of any payments that are due in 30 days from the day the loan was granted, and also show those that are overdue by 10 days from the day they were due. Additionally, I want excel to automatically increase these payments that are overdue by 10 days. Do you perhaps know how one could go about building such?
Awesome Sipho. Thanks for commenting!
Hi Rob, just wondering if you can help me, I have a spread sheet whereby I
how do you exclude these conditional formats from including cells without any current values in it. I'd like to apply these formats to my entire sheet, but there are many cells that could be potentially left blank. Those cells show up as red, so much of my sheet is red.
I just figured out a way. I made another formatting rule that said if a cell was blank to fill it with white.
Conditional formatting is very useful for invoices' due dates.
How do I get it to stop highlighting a row when theres nothing in it
Create a 4th condition that indicates if the cell is blank "", for example = $c4 = "" and then have the formatting settings be no fill.
I'm trying to arrange a function based on days kids ate from most often to least.. what function can I use
How can I create a calendar that allows me to add a follow up office visit of 28 days after the first visit?
Thank you sooooo much! You just made my job so much easier