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

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?

Love this video, actually helped me

@HashAliNZ

Жыл бұрын

Thanks!

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.

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

You're a life saver!!!

@HashAliNZ

Жыл бұрын

Happy to help!

Thank you for the video!

@HashAliNZ

2 жыл бұрын

My pleasure!

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

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

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

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!

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%

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

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

@HashAliNZ

2 жыл бұрын

Most welcome

Big help.. thanks

@HashAliNZ

2 жыл бұрын

Glad it helped

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

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

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?

Appreciate🙏🙏

@HashAliNZ

2 жыл бұрын

You're welcome!

@SumitGoswami

2 жыл бұрын

@@HashAliNZ please share your contact details man.

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.

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!

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.

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

Can you do it with expiration of hours please?

@HashAliNZ

7 ай бұрын

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

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

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

@HashAliNZ

2 жыл бұрын

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

👏

@HashAliNZ

2 жыл бұрын

👍🏼

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!

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

@HashAliNZ

2 жыл бұрын

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

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

@HashAliNZ

10 сағат бұрын

Delete the data in C3

@AlanRodriguez-e3v

4 сағат бұрын

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