No video :(

Google Sheets - Highlight Expiration or Due Dates

Подписаться 15 тыс.

Просмотров 30 тыс.

50% 1

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 ↓↓↓

Опубликовано:

18 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 68

@ImprintGMG 2 года назад

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 2 года назад

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

@SCMC_johnuy 3 дня назад

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

@TanyaCalitz 19 дней назад

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!

@vincenthoudeville5209 2 года назад

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 2 года назад

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!

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

@aidanparente2046 23 дня назад

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

@HashAliNZ 23 дня назад

Hi Aidan, try out the eomonth function 👍

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

@donaldmancera 2 месяца назад

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

@GoingGreenMom 12 дней назад

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

@mitchellgarrison2978 2 года назад

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?

@HearToGrin 2 года назад

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 2 года назад

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!

@pumkawashere 2 года назад

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 2 года назад

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

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

@LifeWithLocc 2 года назад

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 2 года назад

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

@muhammadsameer883 18 дней назад

excellent

@saboorawan1382 2 года назад

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

@HashAliNZ 2 года назад

Most welcome

@janot-tv247 3 месяца назад

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 2 месяца назад

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.

@vllflrps Год назад

You're a life saver!!!

@HashAliNZ Год назад

Happy to help!

@quophibartels 2 года назад

Love this video, actually helped me

@HashAliNZ Год назад

Thanks!

@Sarah-ny1ug 2 года назад

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

@HashAliNZ 2 года назад

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

@aprilannesquejo9572 2 года назад

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

@ottoclarke95 2 года назад

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

@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%

@minhnguyenandrew 2 года назад

Thank you for the video!

@HashAliNZ 2 года назад

My pleasure!

@alanbrigham 8 месяцев назад

Can you do it with expiration of hours please?

@HashAliNZ 8 месяцев назад

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

@user-lu5nb4um8m Год назад

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

@karlbenson 2 года назад

Big help.. thanks

@HashAliNZ 2 года назад

Glad it helped

@smoshyfever6614 2 года назад

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 2 года назад

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

@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

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

@WES_5150 15 дней назад

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

@HashAliNZ 13 дней назад

=[date] < today()

@SumitGoswami 2 года назад

Appreciate🙏🙏

@HashAliNZ 2 года назад

You're welcome!

@SumitGoswami 2 года назад

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

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