Google Sheets - Highlight Expiration or Due Dates
Вставка
- Опубліковано 7 лип 2024
- Learn how to use conditional formatting to highlight dates to show when items expire or when they are due. We will walk through several examples to show you different methods.
🔗 Shared Google Sheet used in the video - bit.ly/highlight-dates
🕒 Timestamps:
0:00 Intro
0:19 Color Range - Relative Highlighting
2:22 Due Before a Certain Date
3:33 Due X Days After Today
4:49 Highlight Another Column
7:13 Highlight Bands of Date Ranges
9:36 Next Video - Custom Number Formatting
Learn more from Prolific Oaktree:
🌳 Next Video - Google Drive - Free vs. Paid Versions • Google Drive - Free vs...
🌳 Related Playlist - Google Apps | Full Tutorials - bit.ly/google-tutorials
#googlesheets #spreadsheet #tutorial - Наука та технологія
Your tutorials are so helpful! I used the information in this video and your others about conditional formatting to create a comprehensive, interactive, and automatically updating To Do list for all of my college assignments. For once I feel properly organized and ahead of what I need to do for my courses. Thank you!
I'm glad to hear that the videos have helped you out. Best of luck in your courses!
I am a developer BTW but really love to keep fiddling with Google sheets in various ways. Was looking for some complex formatting rules based on Dates and found your video really amazing. Thank you!
Glad it was helpful!
Thank you so much! You are the best educational teacher I know!
How can have a date cell highlight if a certain number of days goes by from the date in the cell? It needs to update based on the date in the cell since that is what will be updated frequently. For example; I want the date cell to turn red if 35 days has passed since that date.
Thanks in advance!
Thank you very much, this is very helpful, it has exactly what I need!
Excellant! Just what I was looking for.
Thanks Prolific. Exactly what I needed!
Glad it helped!
Thank you! Very helpful.
5:53 - 7:05 (creating a custom formula) is so helpful and non-intuitive... if you haven't yet, considered doing a short video just on that tip 👏🏽
Concise, clean and effective. Many thanks
Many welcomes!
Hi, i'm loving the information in this video.
Is there a way for me to write a custom formula like the one you are writing at 6:50 in this video but between 2 dates? I'm trying to highlight a name when the date is between 11 and 12 months old (-334 to -366 days).
Thanks in advance.
really I like your tutorials, warm thanks...!
Thanks, I'm glad you like them!
Very useful... Helped me alot... Thank you sir ..
Hello! Thanks for this great tutorial. :) Could you let me know if it's possible to get a notification based on the date within the cell? Like a reminder. I tried Add Reminder extension but was not helpful in my case, as there were old dates within the spreadsheet / multiple dates, I received 100 emails! I want to decide the cells this would be activated to.
Hello! I loved your tutorial, very helpful. But I have a question and I'm hoping you have an answer. On the last part of your tutorial, where you separate the Due Dates between, less than 30 days, between 30-60 days and 61 days above, is there a formula where the amounts can be totaled?
For example, I need the total amount to be paid of vendors who's Due Date passed less than 30 days?
My boss wanted me to have this and your kind response will he highly appreciated. Thank you 🙏🏻
Im having some troubles with making the colors work.
If I have a date in cell E7 listed as 01/31/2023 how do I make the entire row 7 become a color IF that date has surpassed.
I can use the "Date after" rule, but then its only the cell E7 thatll be colored
I have a column labeled Date/Time it is formatted MM/DD/YYY HH:MM.
I want to do a conditional formatting by time to highlight any entry that is between 5:00 PM and 10:00 PM... What is the formula I would use for time?
crystal clear
This is great! Is there a way to use conditional formatting to highlight the 3 cells with the oldest dates? I'm trying to set up a sheet to make sure my managers are checking in on every location on a regular basis. I want a way to flag any location they haven't checked in over 4 weeks (like you showed here). But I also want to flag the 3 locations they haven't checked in the longest time, regardless of whether that's withing or beyond the 4 weeks from the other filter.
This is so helpful! I have a question, is there a way to use conditional formatting to add years to a date? I am trying to track training done and I want people to put in the date they completed a certain training. I then want the next column to automatically calculate the expiry (2 years later). When I put in the formula =EDATE(A1,12*2), the cell automatically fills out to "12/20/1901". I want it only to fill out if the previous cell was filled out. Any advice?
No, conditional formatting can’t change cell values.
Hello! Is there a way to exclude blanks in your =J10>TODAY()+7 formula that you used for the book titles? I’ve been looking for a solution for this for days! 😩 Thank you! 🙏🏼
Probably start with the AND function, the TODAY test is one test for the AND then the ISBLANK function could be the second. Best of luck.
@@ProlificOaktree I have a date column P next to a blank column Q. I’ve come up with this formula: =P2:P
How do I send emails based on these highlights? Is there way to add a rule, and send an email or multiple emails when I am 60 days out 30 days out etc? Thank you
Not sure, but if you could do that, it would be with Apps Script.
Hello what about if I want to highlight the dates that are including within the month. For example Today's october and I want to see all the due date in the cell that is within october? Hoping someone can help me, been looking for a solution for days now :(
Maybe something like running the MONTH function on the date and checking to see if it is equal to 10.
Hi there! How can I highlight the entire row if the date is past due? I've tried a combination of "date is before yesterday" type formulas - without quotes of course! Ha! Just can't seem to get the entire row to highlight, just the cell :/
Hey there, try this video Google Sheets - Conditional Formatting Entire Rows | Text or Dates
ua-cam.com/video/WM4hgenP8g4/v-deo.html
I like your drink your juice and eat the tomatoes analogy
Hey, I'm looking out for you guys! Although it's probably better to eat fruit whole instead of drinking the juice....oh well.
IS IT POSSIBLE TO UPDATE THE COLOR ONCE THE Status on another column is payed
Take a look here if you are wanting to format a cell based on the value in another cell: ua-cam.com/video/W7wjXGzB4hs/v-deo.html
How can one make sure the calculation excludes weekends and holidays?
Probably something that checks to make sure the WORKDAY function result is between 2 and 6. See here sheetshelp.com/weekday/