Problem with conditional formatting and dates

[Thirith]

I've got the following set of conditional formatting rules:

Problem with that is that for some weird reason, there are fields that aren't coloured: for instance, today is 6 March 2018 and the fields for 14 March 2018 aren't colour coded.

I assume the reason is that date values don't just check day, month and year but also time, so that any value that's over seven days but under eight days would fall between two rules. How do I best avoid this and make sure that only the date is considered in the calculation?

 

[Fluff]

Not sure, but try Today instead of Now

 

[Scott T]

My mistake please ignore

 

[Thirith]

Today doesn't seem to work (I can't find it in the list of built-in functions either), but I've tried the following: DateValue(Now()), and that seems to work. Not exactly elegant, mind you...

 

[Joe4]

I don't know if it is my computer, but I do not see your rules in your original post (some images do not come across for me).
What exactly is your Conditional Formatting formula?

 

[Fluff]

The formulas look like

Excel 2013 32 bit
	A
1	Value
2	Value is between Now() and Now()+7
3	Value is between Now() +8 and Now()+14
4	Value is between Now() +15 and Now()+21
5	Value > Now()+21
New

 

[Thirith]

I was using a set of rules like:
Value is Between Now() and Now() + 7
Value is Between Now() + 8 and Now() + 14
Etc.

 

[Joe4]

I believe that Access likes "Date()" instead of "Today()".
So try substituting "Date()" in where you have "Now()".

 

[Thirith]

Fantastic, that worked and is definitely more elegant than DateValue(Now())!

 

[Joe4]

Yes, I always have to think twice when thinking about how each system wants to return today's date without time.
I often mix them up! (Excel vs. Excel VBA vs. Access vs. Access VBA).