IF/AND between two dates Conditional Formatting

3
votes

I am trying to implement the following logic but my formulas are not encompassing all possibilities.

Am I able to accomplish the following using the approach outlined below?

C5 has a start date(yy/mm/dd), 2013-10-01.
D5 has an end date(yy/mm/dd),2013-10-23.
F3->CX2 has 7 cells merged with the monday's date in the cell.

Example: F3: 30-Sep-13, M3: 07-Oct-13, T3: 14-Oct-13.

F4->CX4 and below are the unmerged seven cells, therefore you have seven cells below each week cell.

My goal is to use Conditional Formatting when cell has value TRUE to fill the background colour of each individual cell between the start and the end date. However I am having trouble determining the correct formula. I have tried the following in F4 and across and below but none have proven to work for all scenario's:

F4=AND(C5=F3, D5>=F3) F5=AND(C5=(F3+1), D5>=(F3+1))...

only fills the cell for the start date not all the cells between the start and end date.

In short, I need to identify the start cell and fill it, continue filling all cells until the end date.

Should this be a macro with a while loop?

***I Believe I have solved my question with the following formula:

=IF(AND((F3)>=$C$5,(F3)<=$D$5),TRUE,FALSE), =IF(AND((F3+1)>=$C$5,(F3+1)<=$D$5),TRUE,FALSE), =IF(AND((F3+2)>=$C$5,(F3+2)<=$D$5),TRUE,FALSE)...Then after 7 cells it becomes: =IF(AND((M3+1)>=$C$5,(M3+1)<=$D$5),TRUE,FALSE), =IF(AND((M3+1)>=$C$5,(M3+1)<=$D$5),TRUE,FALSE)

3
excelif-statementexcel-formulaworksheet-functionconditional-formatting
Are you using this formula in an individual cell, or as part of the conditional formatting Format Conditions/rules? - David Zemens
I am putting a formula in the cell that will return TRUE/FALSE, then conditional formatting based on the TRUE/FALSE. - AM_Hawk
@pnuts, sorry that's CX4,it corresponds to 30-Dec-2013 - AM_Hawk
Please don't try to put formulas or code in the comments... - David Zemens
@pnuts Here is a prettier version of what I am trying to do: link - AM_Hawk

3 Answers

1
votes

Should this be a macro with a while loop?

It seems it does not have to be, though that depends upon my interpretation of your question, which seems odd if only because the conditions are not in the rows for which they trigger the formatting.

Select the applicable range starting in F4 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=AND($F$3+COLUMN()-6>=$C5,$F$3+COLUMN()-6<=$D5)

Format..., select choice of formatting, OK, OK.

SO19435562 example

0
votes

Here's a stepwise view on the problem as I see it:

  1. Set up your data and include the actual date of your "weekly view" as part of the sheet:

    enter image description here

  2. Add conditional formatting to a single cell (say, Monday of Week 1) that applies to "a formula to determine which cells to format":

    enter image description here

  3. Copy-and-Paste-formats to the remainder of the cells:

    enter image description here

    The result should resemble:

    enter image description here

  4. Change the actual cell formatting to "" to remove the date from view:

    enter image description here

The removes the capability to insert any content in the cells (but that wasn't part of the question). If you want to insert content and maintain colouring, you can base the conditional formatting on a similar-sized/shaped layout elsewhere in the sheet and format it accordingly. For example, the following layout provides this yet allows you to enter content in the conditionally formatted area:

enter image description here

0
votes

Use the formula conditions. I put a start date in A1, and an end date in A2. Modify as needed per your requirements.

Then I will apply conditional formatting to values in range E1:E6. Again, modify as per your requirements.

enter image description here

Then simply use Highlight cells Rules > Between

enter image description here

Then, select your start/end values and press OK. Voila!

enter image description here