Hi Jon.. great follow up to the first video. Using AND and OR gives more finite control of the conditions to format. Thanks for all your videos.. good learning. Thumbs up!
Hello, I am struggling to apply conditional formatting to an excel sheet. Can you assist? I have data (dates in lets say columns A (Start Date) & B (End Date). To the right of each lines are dates through the end of 2024. I have a total of 75 lines that will be the range. As I receive new date details, and after entering these two date ranges, I want the worksheet to automatically highlight the worksheet between the start and end dates (yellow), so I can see at a glance when people are scheduled for arrivals and departures. I had this set up before but apparently when I added rows, formulas got skewed, and I cannot figure this out. My two date sources are within each line and not at referenced at the top as in your video.
Thank you very much for your very informative video. I'm still having issues with a significant number of cells that fall within the range of dates and either do not format with correct color format or fall on the last day of the selected date and are colored with the wrong color. Note - for the dates outside the selected range I did not use the "=" sign since I don't want them included. Any advice on how/why the formula only applies to certain cells and not others? Thanks again for your excellent video.
If I want a date outside of the start or end date, wouldn't I just use < & > without the equal sign as the equal would include both the start and end dates that I am trying to avoid?
Hi Jon! Thanks a lot for the videos. I just got a question on conditional formatting. Is it possible to apply the format into an entire column? You see, I have a table referencing values in B:C for conditional formatting which are binary "Yes" & "No" data. I would like to highlight the entire row A:H if corresponding rows are TRUE in boolean logic. I tried in I:I formula "=AND(B:B="Yes",C:C="No")" and for rows showing TRUE nothing happens using the same formula applied to "A:H" in Conditional Formatting Rules Manager. Is this possible or I'm doing something not quite right? Please advise. Thanks!
Hi Antonio, Great question! Yes, you can apply the conditional formatting to the whole column. However, the formula will still just reference one cell. It also needs to start in row 1 when you put a whole column reference (A:H) in the Applies to box. Here is what the formula would look like based on my example. =OR($C1=$K$4) The active cell will need to be cell C1 or in row 1 when you modify/create the rule. It's a bit tricky, but does work. I hope that helps. Thanks again and have a nice weekend! :-)
@@ExcelCampus thanks again! I was referencing the whole column thats why Excel is taking its time and decided not to do it cos Im asking too much hahah... It worked like magic when I did just as you said.🤟🏾
HI! Really very well explained I have 2 questions- How can I color cells in another with information in another sheet? I have a task performed by employees in different offices, how do I color the rows with start date and end date? your help will be of great value. Thanks
Hey Jon. I’m trying to do something similar to this. I’m trying to create a monthly budget sheet where my monthly bills are all recurring on the same day every month. Is there a way that I can switch the value in the date column from a fixed date to a recurring date?
i want to create a dynamic chart in excel from single continuesly changing cell value (Like Share market Chart) is it possible directly in excel or through macro Pls HELP.
Not sure why it does not work for me. I gave the AND statement with the following example. I wanted to evaluate that 3/15/2024 is greater than 1/1/2024 and less than 12/31/2024. But it returned "False". Am I doing something wrong with the date format ? Do I need to check something else related to formatting before the formula works properly.
Hi Amita! 😊 If your data is in Column C like in the video for conditional formatting, you could try something like =AND($C7>$C$3,$C7<$C$4). If the date is in a "d/m/yyyy" format, you could test the formats out by using 5/5/24 and 7/3/24 as your start and end dates, with 6/4/24 as your data. Then, switch the start and end dates, and see the result. Use Ctrl+1 to check the format of your cells. I hope that helps! 🙂
Is it possible to fill where I have marked in the actual columns from the range from say Aug-21 to Apr-22 in a colour PHASE START PHASE END Jul-2021 Aug-2021 Sep-2021 Oct-2021 Nov-2021 Dec-2021 Jan-2022 Feb-2022 Mar-2022 Apr-2022 May-2022 Jun-2022 Jul-21 Jun-22 Jul-21 Feb-22 Jul-21 Feb-22 Sep-21 Apr-22 Jul-21 Feb-22 Jul-21 Feb-22 Aug-21 Apr-22
Hi Jon.. great follow up to the first video. Using AND and OR gives more finite control of the conditions to format. Thanks for all your videos.. good learning. Thumbs up!
Thanks so much Wayne! 🙂
Hello, I am struggling to apply conditional formatting to an excel sheet. Can you assist? I have data (dates in lets say columns A (Start Date) & B (End Date). To the right of each lines are dates through the end of 2024. I have a total of 75 lines that will be the range. As I receive new date details, and after entering these two date ranges, I want the worksheet to automatically highlight the worksheet between the start and end dates (yellow), so I can see at a glance when people are scheduled for arrivals and departures. I had this set up before but apparently when I added rows, formulas got skewed, and I cannot figure this out.
My two date sources are within each line and not at referenced at the top as in your video.
thank you so much for your help. The info in this video is carefully conveyed in an instructive and a highly digestible manner.
Very helpful 🙏
Glad it was helpful @PayongKaibigan! 😀
Thank you very much for your very informative video. I'm still having issues with a significant number of cells that fall within the range of dates and either do not format with correct color format or fall on the last day of the selected date and are colored with the wrong color. Note - for the dates outside the selected range I did not use the "=" sign since I don't want them included. Any advice on how/why the formula only applies to certain cells and not others? Thanks again for your excellent video.
Very well explained. Thank you
Great video, thank you Jon!
Thanks my friend! 🙂
That’s nice, I really like your explanation.
Very helpful. Thank you!
This is FANTASTIC!!
Great Video, it was very informative. If I want to filter between two dates and copy the result in another worksheet
Great video, thank you
As always, great video Jon!
Great, thank you John
Thanks Katerina! 🙂
Very useful thanks!
Nice, thank you Jon.
Excellent!
Thanks Yulin! 🙂
thank you Jon
Good one!
Thanks Caroline! 🙂
If I want a date outside of the start or end date, wouldn't I just use < & > without the equal sign as the equal would include both the start and end dates that I am trying to avoid?
Ah yes, you are correct. Thanks for pointing that out! 👍
exactly what I was thinking
Amazing
Great, very good!
Easy nice and clear
Hi Jon! Thanks a lot for the videos. I just got a question on conditional formatting. Is it possible to apply the format into an entire column? You see, I have a table referencing values in B:C for conditional formatting which are binary "Yes" & "No" data. I would like to highlight the entire row A:H if corresponding rows are TRUE in boolean logic. I tried in I:I formula "=AND(B:B="Yes",C:C="No")" and for rows showing TRUE nothing happens using the same formula applied to "A:H" in Conditional Formatting Rules Manager. Is this possible or I'm doing something not quite right? Please advise. Thanks!
Hi Antonio,
Great question! Yes, you can apply the conditional formatting to the whole column. However, the formula will still just reference one cell. It also needs to start in row 1 when you put a whole column reference (A:H) in the Applies to box.
Here is what the formula would look like based on my example.
=OR($C1=$K$4)
The active cell will need to be cell C1 or in row 1 when you modify/create the rule. It's a bit tricky, but does work.
I hope that helps. Thanks again and have a nice weekend! :-)
@@ExcelCampus thanks again! I was referencing the whole column thats why Excel is taking its time and decided not to do it cos Im asking too much hahah... It worked like magic when I did just as you said.🤟🏾
HI! Really very well explained
I have 2 questions-
How can I color cells in another with information in another sheet?
I have a task performed by employees in different offices, how do I color the rows with start date and end date?
your help will be of great value.
Thanks
Hello Jon
How can we highlight a row based on the output produced from another condition?
Please reply......
Hey Jon. I’m trying to do something similar to this. I’m trying to create a monthly budget sheet where my monthly bills are all recurring on the same day every month. Is there a way that I can switch the value in the date column from a fixed date to a recurring date?
i want to create a dynamic chart in excel from single continuesly changing cell value (Like Share market Chart)
is it possible directly in excel or through macro Pls HELP.
Not sure why it does not work for me. I gave the AND statement with the following example. I wanted to evaluate that 3/15/2024 is greater than 1/1/2024 and less than 12/31/2024. But it returned "False". Am I doing something wrong with the date format ? Do I need to check something else related to formatting before the formula works properly.
Hi Amita! 😊 If your data is in Column C like in the video for conditional formatting, you could try something like =AND($C7>$C$3,$C7<$C$4). If the date is in a "d/m/yyyy" format, you could test the formats out by using 5/5/24 and 7/3/24 as your start and end dates, with 6/4/24 as your data. Then, switch the start and end dates, and see the result. Use Ctrl+1 to check the format of your cells. I hope that helps! 🙂
My dates are in columns in one row & my data falls under that column. So how can i do this column wise?
can dwe use if formule intead of aand function?
Is it possible to fill where I have marked in the actual columns from the range from say Aug-21 to Apr-22 in a colour
PHASE START PHASE END Jul-2021 Aug-2021 Sep-2021 Oct-2021 Nov-2021 Dec-2021 Jan-2022 Feb-2022 Mar-2022 Apr-2022 May-2022 Jun-2022
Jul-21 Jun-22
Jul-21 Feb-22
Jul-21 Feb-22
Sep-21 Apr-22
Jul-21 Feb-22
Jul-21 Feb-22
Aug-21 Apr-22
does it work for rows?
Hi Varun! 😊This does work for both rows and columns. I hope that helps! 🙂
@@ExcelCampus Yes, it works. Thank you.
Great video, thank you Jon!
Thanks Natella! 🙂