Forum Discussion
Conditional Formatting Days since Date
I have a client spreadsheet and I am trying conditionally format the cells to highlight in color based on the number of days that have passed since the "Drop off" (Column F) in the same cell/column (Column J)
Yellow: 0-10 days
Orange: 10-12 days
Red: 14 days
Can you add a formula to one cell that adds 10 days, 12 days and 14 days to the "drop off" date and color them accordingly or do I need to make different columns for the different days added?
I hope this make sense. Please help?🤔
7 Replies
Your description confuses me. What does column F contain and what does column J contain?
And which column do you want to format conditionally?
Hi Hans
Column F contains the drop off date, Column J contains the formula F27+10. My problem is that they want column J to change color according to how many days has passed from the drop off date.
Yellow +10 Days
Orange +12 Days
Red +14 Days
Would I need to make an extra column to accommodate the +12 and +14 days or is it possible to combine everything in one formula?
I hope this makes more sense
Hi Hans,
Column F contains the drop off date, column J contains the function =F27+10. I need to format column J, which I can do, but the challenge I have is that I need to format it so I have different colors for different days added to the drop off date all in the same cell
Yellow for +10
Orange for +12
Orange for +14
That is why I asked if you can have more than one formula in a single cell e.g. =F27+10, =F27+12 and =F27+14 and then color code them accordingly. Because my logic say that I would need 3 different columns for each days added. In practice I need the following, if 10days has passed from drop off date then the cell must change color to yellow, 12 days has passed change to orange and after 14 days after drop of it must turn red.
I hope this makes more sense
Thank you. Let's say you want to apply this to J27:J100.
Select this range. J27 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=J27>=F27+10
Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.Repeat these steps, but with the formula
=J27>=F27+12
and orange as fill color.
Finally, repeat with
=J27>=F27+14
and red as fill color.
