Link to home
Create AccountLog in
Avatar of ssblue
ssblueFlag for United States of America

asked on

Excel conditional formating based on a date, in a cell with a formula

I would like to format a cell based on a specific date - the cell has a formula in it.

Example: Cell R2 has a formula in it
I would like to have it turn 'red' if it is past '4/15/2017'
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi,

Use below formula:
=$R2>DATE(2017,4,15)

Open in new window

User generated image
Avatar of ssblue

ASKER

there is no date in the cell  -  only a number which is a sum
You mentioned
I would like to have it turn 'red' if it is past '4/15/2017'

Can you post screenshot?
Avatar of ssblue

ASKER

Example

conditional formatting:  IF cell R2 is past 4/15/2017 then turn red.
User generated image
...so for every cell I will have to create a separate condition using individual dates.
please post this workbook as it is not conventional dates to find a workaround
gowflow
Also which rows and columns should be turned Red, if R2 is past 15?
What is the formula in Row 2?
Avatar of ssblue

ASKER

... based on what I understand I will need individualized conditional formatting for each cell

R2 is just one example. I hope to be able to use the info for R2 to do the same for all the cell on Row 2.
Time-Keeping.xlsx
Do you mean like below:
User generated image
Avatar of ssblue

ASKER

YES!
Avatar of ssblue

ASKER

...actually everything past today should be red.
Just add another Conditional formatting like below image beside your existing:
User generated imageSee attached, I have changed values to check. You can turn back to original
ssblue_Time-Keeping.xlsx
In Row 2 you don't have date, its just a Sum formula. On what row you want to determine past today?
Avatar of ssblue

ASKER

Almost, I need it to be based on the date not a number because the number (sum) could be anything.
Please highlight those cells manually, which should turn Red comparing which cells.
Avatar of ssblue

ASKER

I don't have a date to compare it to - I was hoping I could put the date as part of the conditional formula
Avatar of ssblue

ASKER

AFK for a few hours.
As Row 2 is not in proper date format, shall I highlight Row 4 where your dates are like below image:
User generated imagessblue_Time-Keeping_v1.xlsx
Avatar of ssblue

ASKER

I guess that would work. What I am trying to do is highlight in RED when a day is in the past. I guess I should have just said that in the beginning and let you tell me the best way to do it.
Just change below formula in Conditional Formatting:
=IF(LEFT(D4,2)>TEXT(TODAY(),"dd"),TRUE,FALSE)
to
=IF(LEFT(D4,2)<TEXT(TODAY(),"dd"),TRUE,FALSE)
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ssblue

ASKER

That will work. Thanks!