none
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.

    Thanks in advance for your help!

    Tuesday, January 31, 2012 1:15 PM

Answers

  • =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 renatoabc Wednesday, February 01, 2012 1:52 PM
    • Marked as answer by Rex ZhangModerator 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 renatoabc Wednesday, February 01, 2012 1:52 PM
    • Marked as answer by Rex ZhangModerator 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 renatoabc Wednesday, February 01, 2012 1:58 PM
    Wednesday, February 01, 2012 1:55 PM
  • Dear Rosadocc,

    Can we expect your response for the above posts ?

    Wednesday, February 01, 2012 2:25 PM