Google Sheets - Highlight Expiration or Due Dates

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.

🕒 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:

#googlesheets #spreadsheet #tutorial

[GeorgeKaoCommunity]

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 👏🏽

[SudhanshuSingh]

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!

[jonathanscott]

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!

[philipdeer]

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!

[danellis]

Concise, clean and effective. Many thanks

[amypareezer]

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.

[ericchasseray]

Thanks Prolific. Exactly what I needed!

[vishnusiddharthsrikanth]

Thank you so much! You are the best educational teacher I know!

[birchmcmullin]

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.

[exanimumque]

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

[McintoshSteveAndrea]

Excellant! Just what I was looking for.

[Thedracaenae]

Thank you very much, this is very helpful, it has exactly what I need!

[purplecuriosity]

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 🙏🏻

[RCsunshinelady]

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.

[bagginsbandb]

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?

[easymoveaccounts]

really I like your tutorials, warm thanks...!

[RCsunshinelady]

Hi, i'm loving the information in this video.

[nunufat]

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! 🙏🏼

[aniseanthony]

I like your drink your juice and eat the tomatoes analogy

[bigbadwolves]

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