Google Sheets - Highlight Expiration or Due Dates

Highlighting important information isn't just about aesthetics; it's about clarity, efficiency, and mastery over your data. In this video, we'll dive into the ultimate Google Sheets HOW TO, teaching you how to format and highlight your data in effective and visually appealing ways.

----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Intro
0:24 Setup
1:00 Getting efficient!
2:00 How to format paste
2:35 Formatting dates
3:20 Conditional Formatting
4:30 Improving the conditional format
5:30 Outro

----------------------------------------------------------------------------------------------------

💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓

[aarongrubbs]

I really hope you're still answering comments on this video because I have a problem. I'm making an sheet that is basically calendar for my law school assignments. Across the horizontal axis at the top in line 1 are dates ("Mon 8/19" etc). Along the vertical axis in column A are the titles of my classes. In the boxes are the assignments with divided with borders every 5 lines. 4 of those lines are merged to form one large box to write what the assignment is and in the 5th line is a more specific "due:" message with the time of day the assignment is due. For example B2:B5 are merged and B6 shows the specific due time, and that's all for one class. Beneath that is another class, B7:B10 is the assignment and B11 is the due time. I don't have assignment due for every class every day so only some of the "super-boxes" (merged lines with the assignment and the fifth line with the due time) have any actual text in them.

What I want is for the super boxes with actual text in them to turn slightly grey and the text therein to be struck through when today's date is after the date at the top of a given column. I used your formula exactly but weird things are happening. Today is currently Tuesday Aug. 20 (Tue 8/20). My column B is for Monday August 19 (Mon 8/19). In conditional formatting I put apply to range B2:B26. Format rules: Custom formula is: =and($B1<today(), $B1<>"") and the format style set to strike through and turn slightly grey. The problem is that rather it turning range B12:B16 grey and striking through the text in those boxes, the formatting only seems to apply to B2:B5, which is a completely empty range, but not B6, which is the final line in the "super box" and contains the text "Due:". This problem persists in every column. I can change the formatting to only apply to one "super box" at a time (e.g., B12:B16) and then the formatting works for B12:B15, but again as before it still doesn't work for the "Due:" line of the super box (B16 in this case).

What is going wrong here?

[tinaphillips-re]

I'm using a Google sheet to keep track of a point system that expires 30 days after each individual transaction, and I got tired of figuring out the 30 days in my head pretty quickly. Thanks for showing me an easy way to have the sheet calculate it for me!

[ImprintGMG]

love your videos my favorite parts are when something unexpected happens and you try to solve it. it gives it a more real life teacher and class feel. plus its realistic haha.

[TanyaCalitz]

Kia Ora, I have past dates e.g. 02/02/2023 and I would like the expiry to be a year out so 02/02/2024 to be highlighted red if 1 day from expiry, orange if 1 month and green more than a month out. Can you please help with this formula? Thanks!

[mitchellgarrison]

Love the video, very informative!

I'm attempting to set up a conditional format where an employee's certification date is input into F4:F, with the default "good color" being green; the cell would then populate Yellow when the certification is 90-61 days from expiring, Orange when 60-31 days from expiration, and Red when 30 or less from expiration. All certifications are good for (for reference purposes) 365 days. Any ideas?

[DeannaBurns-et]

Is there a way to format cells so that it's showing what is close to expiring? I like to color code mine so that things that expires within 30 days are highlighted in yellow. Thanks so much!

[tclapson]

Hey, I was hoping this would give me what I'm looking for. What I'm after is dynamic conditional formatting. So from today, and the last week to highlight them in one column all one colour. And then the previous week another colour, and the week before that a different colour, until I get to about 6 weeks and then anything that is going to be 6 or more weeks I would like all red. The purpose of this is for repricing products that I sell online. The products that have been repriced recently need less attention than those that are nearing 3, 4 and more weeks. Hope you consider doing a video on it... Many thanks, :)

[vincenthoudeville]

Great tips, thank you. I watched all your videos about the student clocking file and i adapted it to create a clocking machine for my employees which also gives the location where they clocked in and out (i added a script for that). It's so much better than the one we had before and this is free!!

I know you said you live in New Zealand but your accent sounds South African so I was just wondering if perhaps you were originally South African ?

[SCMC_johnuy]

What is the conditional formatting custom formula if I want to highlight cells expiring on CURRENT Month regardless on number of days left?

[jean-baptistegoriou]

Thank you for the very informative video! If I want to have multiple colors for the same column (ex. yellow if it's been 7 days, orange if it's been 14, and red if it's been 30 days or more), how do I write that formula in? Thanks to your video I can do one color but I don't know how to do multiple.

[pumkawashere]

Loved your video! Very helpful! Thank you so much! How do I calculate (in an additional column) how many days (months) are left before the expiration date?

[TheMochaangel]

Hi, I'm looking to create a Google spreadsheet where I have upcoming dates due for multiple people. So say I have 10 people, with all different due date of something, I would like the due date column to reflect like red (due within 14 days), yellow (due within 30 days) and green (due greater than 30 days).... how may I make a condition format for that. ... like column B can be the due date and column C may be the column to reflect the color indicators of coming due. Any support is appreciated.

[AlanRodriguez-ev]

another question brother, I added a drop down on the right of the past due date with yes or no that states the client paid. IS there any way to automatically remove the highlight once I answer/click yes on the drop down?

[Sarah-nyug]

Thank You. I want to create something that track due dates every 10 working days. Is. This possible?

[aprilannesquejo]

Hi Sir, do you have a formula for the same day of each month's due date? for ex> I have payments that Will be due every 15th of the month and I want them to be automatically updated based on the data validation ex>. PAID

[aidanparente]

Great Video! how could you change the today to last day of the month - for conditional formating

[LifeWithLocc]

Hello! I am trying to set up a sheet where the cells turn red on and after 90 days, yellow between 60 and 89 days, and green if greater than 90 days. Any advice?? Thank you!

[TandraLogiudice]

loved this video and it got me started, however, I wanted to take it a step further and need assistance. I am working with an invoice tracker that has (Date Billed, Billing due date, and date paid). i am wanting to color code my bill due date to reflect when a bill is coming up or past due in colors red, yellow, and green but to have this column be contigent on the date paid column. for example: the invoice is late (cell turns red), invoice is paid and notated in date paid, I would like to see that same red cell turn green after payment. any suggestions on this?

[ottoclarke]

Thanks so much for the explanation. What would I do to turn the rows green once they have been paid? (I have a row which I input the date when it has been paid)? Thanks!

[janot-tv]

How to enumerate dates in between two dates, like for example

Jan 2 2024 - Jan 6 2024

I would like to know if there is a formula that can show me the date (Jan3, 4, and jan5.

Thanks