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.
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
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?
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!
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.
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.
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""
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.
@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())
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
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.
Great!!! Thanks for everything Chandoo!
It helped me a lot in monitoring the expiration of staff licenses.
it is useful. We need to know after setting the conditional formatting, will it get highlighted automatically
Excellent Video, Thanks a lot! Where can I find the detailed video of the form combo box and radio buttons?
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
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?
Can you assist. I have followed all the rules, however, I cannot get the highlighted colours.
same here
This was fantastic!!!
How is that median function calculates whether its within a week i.e 7 days?
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?
Is there any way of creating a kind of separate "summary" sheet to show lets say expired and expiring in
You can apply the ideas discussed here to get what you want. ua-cam.com/video/kfeFcyDrcvQ/v-deo.html
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!
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.
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.
nice work very good effort thank you
what instead of "done" you have an actual date on that cell? how do you add to your formula?
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""
Can you help me in creating an excel sheet for work purpose.
Sir if am applying same formula but here it is not working.. How can I use this please help about this
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.
How can you use this for time?
In place of done I want to write completion date ,
then what will be formula in place of done.
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.
What if I want it everything over 30 days old?
Set up a condition to check (Today()-yourdate )>30
⭐️⭐️⭐️⭐️⭐️
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.
@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())
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
It’s showing 00-01-1900 after little change in formula
you talk much than action..very poor video
There is an example file & a link in the description. You don't have to listen to everything I say.