Great!!! Thanks for everything Chandoo!

Great Video Thanks a lot, I was trying to format cells with "IF" Formula until I realised that the result wasn't true of False, helped me a lot I used the "AND" and worked like a charm.

It helped me a lot in monitoring the expiration of staff licenses.

nice work very good effort thank you

This was fantastic!!!

thanks so much chandoo! it was really helpful for my work. but is it possible, if I have a begin date, if 40 days later, I didn't done the task, should the row be highlighted, but if like in you video said, after "done", then the color will be disappear. I suppose, if the column is R for the beginn date, it should be "=AND($R2

it is useful. We need to know after setting the conditional formatting, will it get highlighted automatically

ok, so... i have a number of orders placed, each with their own delivery dates. i want each row (each article) to be highlighted when the delivery date is overdue. For example, Product XYZ was due on August 20th and it's already August 21st. What formula do I use so that I can clearly see which order to take care asap?

Excellent Video, Thanks a lot! Where can I find the detailed video of the form combo box and radio buttons?

Can you help me in creating an excel sheet for work purpose.

That is the same issue with me, I have followed all the rules, however, I cannot get the highlighted colours. Could you help me with this?

Can you assist. I have followed all the rules, however, I cannot get the highlighted colours.

@richamaheshwari2147

4 жыл бұрын

same here

⭐️⭐️⭐️⭐️⭐️

Is there any way of creating a kind of separate "summary" sheet to show lets say expired and expiring in

@chandoo_

4 жыл бұрын

You can apply the ideas discussed here to get what you want. kzread.info/dash/bejne/nZqZqMWydNXJprQ.html

How is that median function calculates whether its within a week i.e 7 days?

How can you use this for time?

Can you help me!? I want to highlight a row where one column contains a certain text another that’s 7 days past the date unless one column says complete? If that makes sense. Please and thank you!

@chandoo_

4 жыл бұрын

Interesting question. Assuming column A has the text, B has due date, C can have the word "Complete", follow below steps. 1. Select your range of cells. (I am assuming A1 is the first cell, change references accordingly) 2. Go to Home > Conditional Formatting > New Rule 3. Select "Formula based rule" option 4. Write the rule =AND($B1-today()>7, $C1 "Complete") 5. Set up the formatting you want Click OK. It should apply the formatting.

@jbkregs2766

4 жыл бұрын

That worked great thank you so very much. How would I change a status from “in process” to “late” if it goes past 7 days. I’ve tried nested if statements but it’s not working correctly.

what instead of "done" you have an actual date on that cell? how do you add to your formula?

@chandoo_

3 жыл бұрын

Depends on how you want to use that date. For ex, to highlight activities that are due & have don't have a completion date, replace this part *$F5"Done"* in the rule with $F5""

What if I want it everything over 30 days old?

@chandoo_

4 жыл бұрын

Set up a condition to check (Today()-yourdate )>30

In place of done I want to write completion date , then what will be formula in place of done.

@chandoo_

4 жыл бұрын

You can use completion date to check too. For ex. if the cell is empty then the item is still not done. Give it a try.

Sir if am applying same formula but here it is not working.. How can I use this please help about this

@chandoo_

3 жыл бұрын

I suspect the rule is refering to another cell. You need to select the top-left cell of the range before entering CF rules. Otherwise, the relative nature CF rules will automatically change references. Edit the rule and fix any incorrect references.

What about if due date is different with different month and different year? Like starting date : 01/05/2015 and next due date will be in next year 01/05/2016.....! Or past date was 13/07/2017, present date is 13/07/2018, next due date will be 13/07/2019.

@chandoo_

6 жыл бұрын

@Vivek Mittal: If you want a recurring due date behaviour, then potentially every value is overdue. Let's say you want to check a date like 25th of July every year and highlight whenever due date is with in 30 day window. Assuming Cell A1 has the date (say 25-July-2016, but should highlight today - ie 21st of July 2018 as this is a recurring date), select the cell and apply the below rule: =AND(DATE(day(A1), month(A1), year(TODAY())>=(TODAY()-15), DATE(day(A1), month(A1), year(TODAY())

@vivekmittal5738

6 жыл бұрын

Learn Excel from Chandoo @chandoo thank you for your formula but it not working with error” your formula is missing a parenthesis-) or check the formula

@vivekmittal5738

6 жыл бұрын

It’s showing 00-01-1900 after little change in formula

you talk much than action..very poor video

@chandoo_

2 жыл бұрын

There is an example file & a link in the description. You don't have to listen to everything I say.