Highlight Rows Between Two Dates with Conditional Formatting in Excel

preview_player
Показать описание
In this post I explain how to apply conditional formatting to entire rows in a data range based on the values of two cells, such as a start and end date.
You can easily highlight rows that fall between or outside of the specified dates using AND and OR functions. This works for all both dates and regular numbers.
This technique makes it easy to automatically and dynamically highlight data that meet specific conditions or criteria.


Additional Resources:

#MsExcel #ExcelCampus
00:00 Introduction
00:52 Formulas and Functions
02:55 Apply Conditional Formatting
05:58 Relative Reference Formula
06:35 Conditional Formatting Outside of Two Dates
08:59 Modifying Conditional Formatting
10:21 Conditional Formatting with Numbers
Рекомендации по теме
Комментарии
Автор

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!

wayneedmondson
Автор

thank you so much for your help. The info in this video is carefully conveyed in an instructive and a highly digestible manner.

nikolapilipovic
Автор

That’s nice, I really like your explanation.

quakargue
Автор

Great Video, it was very informative. If I want to filter between two dates and copy the result in another worksheet

khalednaser
Автор

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.

svdmqve
Автор

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.

erichtx
Автор

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

spasbeautiful
Автор

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.

amitamaru
Автор

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?

dennisd
Автор

My dates are in columns in one row & my data falls under that column. So how can i do this column wise?

RishabGoel
Автор

Hello Jon
How can we highlight a row based on the output produced from another condition?
Please

shinev
Автор

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.

jaideepmittal
Автор

can dwe use if formule intead of aand function?

vikramraghuwanshi
Автор

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







Aug-21 Apr-22

amandaaldersonperthwaactor
Автор

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?

paulj.spence
Автор

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!

tonyagamata