# Easy Question?? Conditional Formatting based on a date from another cell.

### Question

• Hi, this is an easy question and I remember finding it somewhere on this forum (might be another forum) but I cant seem to find it so I hope you all dont mind that i post it here.

This might be an easy question but I am wondering how to conditionally format one cell based on another cell, IF that cell falls between a certain range of dates. What would the formula look like?

For example cell A1 lists dates and cell B1 highlights in RED if cell A1 is a date that falls between 1/1/2012 and 1/7/2012.

Tuesday, January 31, 2012 1:15 PM

• =AND(A1>=DATEVALUE("1/1/2012"),A1<=DATEVALUE("1/7/2012"))

Select B1->In Home tab Click Conditional Formatting ->Manage Rules ->New Rule ->Use a formula to determine which cells to format->In Format Values where the formula is true->PAste the above formula->Click Format button and choose the format.

• Proposed as answer by Wednesday, February 01, 2012 1:52 PM
• Marked as answer by Thursday, February 09, 2012 12:36 AM
Tuesday, January 31, 2012 1:56 PM

### All replies

• =AND(A1>=DATEVALUE("1/1/2012"),A1<=DATEVALUE("1/7/2012"))

Select B1->In Home tab Click Conditional Formatting ->Manage Rules ->New Rule ->Use a formula to determine which cells to format->In Format Values where the formula is true->PAste the above formula->Click Format button and choose the format.

• Proposed as answer by Wednesday, February 01, 2012 1:52 PM
• Marked as answer by Thursday, February 09, 2012 12:36 AM
Tuesday, January 31, 2012 1:56 PM
• THANKS! Just what i needed.
Tuesday, January 31, 2012 3:47 PM
• Ok now that I got that formula. I hope maybe you can help me with this new question. I posted a pic of what im working on below:

As you can see I used the formula mentioned above to highlight red for the week (H4) of the Start Date (C4) and blue (L4) for the week of the end date (D4). I was hoping that the weeks in between (cells I4, J4, and K4) can be be highlighed shaded in gray. Is there a conditional format formula I can use to shade in gray if those cells in between fall in between the start date in C4 and due date on D4?

Tuesday, January 31, 2012 4:25 PM
• Pls give a screenshot how you want it to look after applying formula.

BTH will you mantain the fixed 4 week for each month ?How will you treat 27th Feb 2012.In 4th or 3rd or 5th?

Wednesday, February 01, 2012 6:27 AM
• Good point,

Every year has 52 weeks, not 12*4 Weeks.

I propose a new design for the table, maybe better if possible to enter the dates inside the conditional formated cells, much easyer.

Renatoabc

• Edited by Wednesday, February 01, 2012 1:58 PM
Wednesday, February 01, 2012 1:55 PM