ssblue

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'

Example: Cell R2 has a formula in it

I would like to have it turn 'red' if it is past '4/15/2017'

ASKER

there is no date in the cell - only a number which is a sum

You mentioned

Can you post screenshot?

I would like to have it turn 'red' if it is past '4/15/2017'

Can you post screenshot?

ASKER

please post this workbook as it is not conventional dates to find a workaround

gowflow

gowflow

Also which rows and columns should be turned Red, if R2 is past 15?

What is the formula in Row 2?

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

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

ASKER

YES!

ASKER

...actually everything past today should be red.

Just add another Conditional formatting like below image beside your existing:

See attached, I have changed values to check. You can turn back to original

ssblue_Time-Keeping.xlsx

See 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?

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.

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

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:

ssblue_Time-Keeping_v1.xlsx

ssblue_Time-Keeping_v1.xlsx

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)

=IF(LEFT(D4,2)>TEXT(TODAY(

to

=IF(LEFT(D4,2)<TEXT(TODAY(

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

That will work. Thanks!

Use below formula:

Open in new window