Conditional Formatting on Dates

[StuartG]

Hello all,
I have reached a bit of a stumbling block for something that I am trying to achieve.

I would like to use conditional formatting on a date range.

In my tables I have "Opended Date" this is automatically generated when a user creates a record, I then have "Due back field" again a date field, which is manually populated by the user.

I would like to complete the following:

The reporting needs to be done on the "Due back field"
If date is within 7 Days of now = Green
If date is within 4 Days of now = Orange
If date is within 2 days of now = Red

I tried to write this is code format but unable to get it right. :banghead::banghead:

The conditional formatting is on a form..

 

[isladogs]

You need to apply the rules in the reverse order as below

 

[StuartG]

Thank you, this had helped, however It is not quite what i needed. (sorry)

Ideally I wanted it to show the colour all the time, bit like a traffic light system.

If date is within 7 Days of now - Always Green.
If date is within 4 Days of now = Always Orange
If date is within 2 days of now = Always Red

Currently it only shows me green.

What Id like to see is something that Excel provides. (attached for ref)

I have attached two files for reference.

Thanks again

 

[jdraw]

Stuart,

If date is within 7 Days of now - Always Green.
If date is within 4 Days of now = Always Orange
If date is within 2 days of now = Always Red

If a Date is 3 days from now, it is within 4 days of Now And is also within 7 days of Now.
The order in which these tests are applied seems critical.
Can you show the actual code you are using?

 

[StuartG]

Hi Jdraw,

I am not using a particular code persay.
I am typing directly into the form using the conditional formatting option.

Could it field related?
As that particular field is setup as a Date/Time (with a medium format)

Stuart

 

[isladogs]

As I said in my previous reply, you need to apply the rules in the reverse order as below

That makes sure that the 3 colour formats are enforced in turn
any other order will not give the correct results

 

[StuartG]

Hi Isladog,

Sorry, I had not followed it correctly previously.
I have made amendments, find attached.

Am i asking it to do the wrong thing, when I say I would like it to always show green, orange, red etc?

Stuart

 

[isladogs]

PLEASE look carefully at the screenshot I gave.
Use less than or equal to (<=) and not equal to

 

[StuartG]

Hi Isla,

Thank you, and for being so patient.

I have viewed this several times and tried different bits and get the following (attached)

Stuart

 

[Gasman]

Looks like Isla has missed this.

If you look at your picture you have one field that already states Less Than or Equal To, so I believe all you would need is Date() + your number

If you did not have that second field, then the syntax would be correct I would have thought.

Try that.

 

[isladogs]

Apologies. I did make a mistake in post #2.
The third condition should also be less than or equal to (<=)

To apply the condition you do indeed select 'less than or equal to' in the combo then type e.g. Date()+7 in the other section.

Hope that's clear now!

 

[StuartG]

Thanks all, i think I have grasped it now.

However to me it still don't seem right?

There is a date of the 03-07-18, surely this should be red?

 

[isladogs]

No you still haven't got it!

Apply the formats in the order shown in post #2 (as previously stated TWICE)
Use 'less than or equal to' for each statement correcting the error I made for the third condition

Anything more than 7 days from now isn't coloured using these rules e.g. 11 Oct 2018

 

[StuartG]

Hi Isla.
Thanks, and again for the patience.

I attach the outcome

I have added to this outside of what we had discussed.

 

[isladogs]

Hooray. Now you've got it!
You can have up to 50 conditions though doing too many can be confusing.

Hopefully you understand why the order needs to be that shown