Code for date

[toddp2]

Hi All

I'm using this spreadsheet at work for budget monitoring however I want to be able to look at whatever sheet and know if the order has been GRN'd this is what goes in when the order is received, if the order has not been GRN'd within 14 days I would like the date cell to turn red.

Can this be done through conditional formatting or is a piece of code required.

Thanks
Toddp2
[[ATTACH]53794test CE.xlsx8[/ATTACH]

Use Test CE file instead of committed expenditure file
thanks

[Kenneth Hobson]

I am not sure if you wanted it red if not GRN or not. CF formula rule:
=OR($H10<>"GRN",AND($H10="GRN",NOW()-$E10>14))

[toddp2]

Hi Kenneth

That looks the part, its the date cell I want in red and would the 14 represent 14 calendar days

Toddp2

Thought I knew how this works but I don't where on the sheet should apply the formula

[Kenneth Hobson]

Put it in your Date cell row 14 (E10) and after the rule is entered, select all the rows in that column you want the rule to apply. Note how I use $ just for the columns, not the rows.

You can put that formula anywhere to evaluate the rule for E10. The rule should evaluate to True or False. If True, the conditions are met.

Yes, 14 is the difference from Now() to the date in E10. Change to suit.

[toddp2]

Thanks for your help

[toddp2]

Hi Guys

Could someone add this formula to the attached sheet Kenneth explained it but I'm not getting it, the plan is that after 14 days or whatever th edate cell will change to red as a to hightlight the order hasn't been GRN'd


CE Test.xlsx

=OR($H10<>"GRN",AND($H10="GRN",NOW()-$E10>14))

[Richard Buttrey]

It's a conditional format formula, not a formula that goes in a cell.

Home...Conditional Formatting, Manage Rules, New Rule, Use a formula option and enter the formula.

[6StringJazzer]

" the plan is that after 14 days or whatever th edate cell will change to red as a to hightlight the order hasn't been GRN'd"

Could be clearer.

Here is what I did. If it's not what you want you will have to explain further.

Conditional formatting will highlight the cells in the Date column if the GRN column is blank and the date is more than 14 days old. (This is a bit different than what Kennth gave you.) The formula is

Please Login or Register  to view this content.

[toddp2]

Hi 6StringJazzer

That's bang on the money, I will provide clearer explanation within my posts in the future.

Thanks
Toddp2

[6StringJazzer]

I will provide clearer explanation within my posts in the future.

Just for the record here is what I could not tell for sure:

14 days or whatever

Well, I just used 14 days. Excel does not yet have a WHATEVER function

the order hasn't been GRN'd

I don't know what that meant or how to tell. I took a guess that if an order had been GRN'd that something would appear in the GRN column. So I assumed if the column is blank, it has not been GRN'd. (I could also imagine other situations where various values could appear in that column meaning various things.)