Google Sheets - Highlight Expiration or Due Dates

[@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.

[@HashAliNZ]

I was going to edit that blank stare out but decided it's good to show confusion

[@tinaphillips-r2e]

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!

[@HashAliNZ]

Happy to help. You can also filter by date and create a formula that limits the points calculation by date so you don't have to just select the ones that you've conditionally formatted, if that's what you've done with help from this video

[@aarongrubbs5668]

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

[@HashAliNZ]

Hey Aaron, here's the formula you need: =and(B$1

[@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!

[@mitchellgarrison2978]

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?

[@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.

[@DeannaBurns-e6t]

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!

[@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!

[@HashAliNZ]

Hey Locc! I assume you mean 90 days in the past? Here's what we need to do: → Click on one of your cells with the due date. For this example, I'll use A2 so whereever you see A2 below, just change it to the cell with your date. → Go to Format > Conditional Formatting → Change "Apply to range" to A2:A (or whatever column your date is) → Change the "Format cells if..." to "Custom formula is" → In the box below that dropdown box, use this formula: =today()-A2>=90 and set your colour to red. → Click on "Add another rule" → Change the colour to yellow and the formula to: =today()-A2

[@AlanRodriguez-e3v]

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?

[@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?

[@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, :)

[@quophibartels]

Love this video, actually helped me

[@HashAliNZ]

Thanks!

[@jean-baptistegoriou3894]

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.

[@vllflrps]

You're a life saver!!!

[@HashAliNZ]

Happy to help!

[@Sarah-ny1ug]

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

[@HashAliNZ]

I'm sure it is. Can you expand a bit on how you want it to work?

[@HearToGrin]

Oh gosh so weird to see this video popped up while I was struggling with conditional formatting 😂 I wanted to make conditional formatting for fractions (eg. GREEN cells if GREATER THAN OR EQUAL TO 6/9, YELLOW cells if BETWEEN 3/9 and 5/9, RED CELLS if LESS THAN 3/9). Three problems: 1) It recognises fractions as a date format. 2) Fractions simplifying (eg. 3/9 > 1/3). I want exact fractions, not simplified 3) It recognises 0/9 as a plain text.

[@HashAliNZ]

One of the big problems with spreadsheet programs is the auto change from fractions to dates. It's cause lots of problems and incorrect statistics. There are some fixes, but none that I can see would work with your data. To recognise as a fraction and not date, start with an equals symbols. E.g, instead of writing 1/3, use =1/3. This is a problem because it won't keep your 3/9 formatting. I could probably write up a way to solve this, but right now my boy is wanting dinner. I'll work on it and get back to you!

[@saboorawan1382]

Thanks sir. this is very help full video for me. Great video.

[@HashAliNZ]

Most welcome

[@anchoridgerealtyanddevelop4361]

hello there! i would like to know how to do a sparkline formula, but it represents the date today over the end line date. say its november 2 today, and the deadline is december 2, and when we reach halfway, or november 17, the sparkline cell would show 50%

[@janot-tv247]

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

[@HashAliNZ]

Totally: =sequence("6/1/2024"-"2/1/2024"+1,1,"2/1/2024") Just change the dates in the formula to the references where your dates are.

[@ottoclarke95]

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!

[@HashAliNZ]

Let's say the date that it's been paid is in column M, for example. You could use this in your Custom Conditional Format: =$M2"" and set your "Applies to range" field as all the cells

[@vincenthoudeville5209]

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 ?

[@HashAliNZ]

That sounds like a fantastic system, Vincent! Great work! My accent is a mixture of a bunch of different accents. Not South African, though. I'm originally Australian with a mix of ancestry and moved to NZ about 20 years ago. Thanks for watching and commenting!

[@aprilannesquejo9572]

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

[@minhnguyenandrew]

Thank you for the video!

[@HashAliNZ]

My pleasure!

[@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?

[@HashAliNZ]

Absolutely! If you've already got your date section done then fantastic. Now we just need to add another conditional format that reads the date paid column. Your conditional format could either be a custom formula, or you could use the Is Not Empty option. Now when you click on "Done" you'll see the conditional format at the bottom of the list. Drag it to the top to make it the highest priority and you're all set 👍🏽

[@aidanparente2046]

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

[@HashAliNZ]

Hi Aidan, try out the eomonth function 👍

[@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?

[@HashAliNZ]

Glad it was helpful! All you need to do is create a formula that subtracts today's date from your expiration date. So if your expiration date is in cell C2, you could use =C2-today() If you want it in months, we can approximate an answer. It should be good enough. Maybe something like =round((C2-today())/30

[@donaldmancera]

How can we change high lightening to 3 days before due?

[@smoshyfever6614]

Thank you for this helpful video! What formula should I type in if I want the due date highlighted if today's date is 3 days before it? If that makes sense, LOL

[@HashAliNZ]

Using the setup I have, use this in your Custom Conditional Format: =today()=C2-3

[@SumitGoswami]

Appreciate🙏🙏

[@HashAliNZ]

You're welcome!

[@SumitGoswami]

@@HashAliNZ please share your contact details man.

[@michastrzadaa467]

The color under the dates is not removed, the "wrong formula" pops up =and($C2

[@BramvanVliet]

I guess this function is broken, I have the same issue. It gives "Wrong formula"

[@HashAliNZ]

In Europe, Google Sheets uses semicolons instead of commas for separators

[@tonimiu6617]

@@HashAliNZ Perfect. I use this formula =and($C2

[@HashAliNZ]

@@tonimiu6617 Nice!

[@lauriefabbo9433]

How do I add a conditional format for when column I is greater than column E by 5 years??

[@HashAliNZ]

Hey Laurie! We can use the YEARFRAC function to determine the number of years between two dates. Here's the conditional formatting formula to use: =YEARFRAC($I1,$E1)>5 Set your Apply to range to A1: K or whatever you need.

[@karlbenson]

Big help.. thanks

[@HashAliNZ]

Glad it helped

[@kpoppappi409]

What will be the formula if I wanted to produce a red color if the dates are 2 days from today, orange color if dates are 1 week from today, and blue color if 2 weeks from today? Thank youuu 😭🙏

[@HashAliNZ]

Hi Kpoppappi! You'll need three different conditional formats. Let's say the date is in A2 First, the blue one: =$A2=today()+14 Orange: =$A2=today()+7 Red:=$A2=today()+2 Let's say you want everything AFTER two weeks from today to also be blue: =$A2>=today()+14 And similar for the others

[@muhammadsameer883]

excellent

[@GoingGreenMom]

This would have been awesome when I was in charge of keeping driver files up to date.

[@alanbrigham]

Can you do it with expiration of hours please?

[@HashAliNZ]

Sure, depending on what exactly you're wanting. If it's say a number of hours completed, like 40 hours or something, then your conditional format formula would be something like =$C2>40 where column C has the total hours

[@jimmyxiz1646]

Hey I am your old student I’m James fox keep up the vids do u remember me

[@HashAliNZ]

Hey Jimmy! I do remember you. Hope everything's going well for ya!

[@adonimad]

👏

[@HashAliNZ]

👍🏼

[@AlanRodriguez-e3v]

I got a "#REF ARRAY RESULT WAS NOT EXPANDED BECAUSE IT WOULD OVERWRITE DATA IN C3" when putting the formula HALP PLEASE

[@HashAliNZ]

Delete the data in C3

[@AlanRodriguez-e3v]

@@HashAliNZ I appreciate the prompt response bro seriously loved this video needed this at the moment and it helped me a ton I immediately subscribed. Any chance you know how to automatically remove the highlighted section once a question is answered to yes?

[@dadzieemmanuelful]

Can someone please help me with the rule for "3 months before expiry"

[@HashAliNZ]

Months don't work very easily. Instead, go with 90 days.

[@WES_5150]

The condition should be if the date thats in the due date cell is in the past.

[@HashAliNZ]

=[date] < today()

[@BridgettPritchard]

Good information but way too fast. A novice like me could not keep up.

[@HashAliNZ]

Pacing is one of the hardest things to get right. I get messages on the same videos with some people saying it's too slow and some saying too fast. The hot key in youtube to pause is the k key