Conditional Formatting on Dates (1 Viewer)

StuartG

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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..
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
You need to apply the rules in the reverse order as below

 

Attachments

  • Capture.PNG
    Capture.PNG
    11.1 KB · Views: 183

StuartG

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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
 

Attachments

  • Date Capture.JPG
    Date Capture.JPG
    73.1 KB · Views: 55
  • Colour Scales.xlsx
    8.7 KB · Views: 53

jdraw

Super Moderator
Staff member
Local time
Today, 07:17
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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
 

Attachments

  • Date Capture1.2.JPG
    Date Capture1.2.JPG
    64.7 KB · Views: 63

isladogs

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
PLEASE look carefully at the screenshot I gave.
Use less than or equal to (<=) and not equal to
 

StuartG

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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
 

Attachments

  • Date Capture1.3.JPG
    Date Capture1.3.JPG
    81 KB · Views: 59

Gasman

Enthusiastic Amateur
Local time
Today, 11:17
Joined
Sep 21, 2011
Messages
14,050
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

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
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?
 

Attachments

  • Date Capture1.4.JPG
    Date Capture1.4.JPG
    73.9 KB · Views: 54

isladogs

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • Capture.jpg
    Capture.jpg
    82.7 KB · Views: 114
Last edited:

StuartG

Registered User.
Local time
Today, 11:17
Joined
Sep 12, 2018
Messages
125
Hi Isla.
Thanks, and again for the patience.

I attach the outcome :)

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

Attachments

  • Date Capture1.5.JPG
    Date Capture1.5.JPG
    83.6 KB · Views: 60

isladogs

MVP / VIP
Local time
Today, 11:17
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom