Need help with Conditional formatting in MS-XL - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Need help with Conditional formatting in MS-XL

jupe17 Avatar
5y, 9m agoPosted 5 years, 9 months ago
Scenario: I have a task recorded in an XL which is due on say 28th Feb. I want to use conditional formatting to highlight the row item in red, amber or green depending on when its due and the date today.

if today is 25th Feb or before, task is due on 28th, row should be green
if today is 26th or 27th Feb, task is due on 28th, row should be amber
if today is 28th Feb or any date in March and task was due on Feb 28th, row should be red

Can someone help with how to set this rule?
jupe17 Avatar
5y, 9m agoPosted 5 years, 9 months ago
Options

All Comments

(3) Jump to unreadPost a comment
Comments/page:
#1
Not sure, but I would approach it by inserting a column with a formula deducting today() from the target date. Then you can set up conditional formating to say when the number is 3 or greater the cell is green, 1-2 amber, and 0 or less red. The conditional formatting can be on the target date cell but just 'pointing' towards the new calculation column which you can hide if you want. Hope this helps.
#2
Try something along the lines of

Condition 1:-
Greater than or equal to
=today()
Format Red

Condition 2:-
Greater than or equal to
=today()-1
Format Amber

Condition 3:-
Less than or equal to
=today()
Format Green
#3
The problem with doing this is that even if the task is complete, once it reaches the day before it will go amber and once it reaches and exceeds the target date, it will go red.

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!