Problem with conditional formatting and dates

Thirith

Board Regular
Joined
Jun 9, 2009
Messages
115
I've got the following set of conditional formatting rules:

VmaDXPq.png


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?
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Not sure, but try Today instead of Now
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
My mistake please ignore
 
Last edited:

Thirith

Board Regular
Joined
Jun 9, 2009
Messages
115
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
The formulas look like

<b>Excel 2013 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1</td><td style=";">Value<now()</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">2</td><td style=";">Value is between Now() and Now()+7</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">3</td><td style=";">Value is between Now() +8 and Now()+14</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">4</td><td style=";">Value is between Now() +15 and Now()+21</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">5</td><td style=";">Value > Now()+21</td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">New</p><br /><br />
 

Thirith

Board Regular
Joined
Jun 9, 2009
Messages
115

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
I believe that Access likes "Date()" instead of "Today()".
So try substituting "Date()" in where you have "Now()".
 

Thirith

Board Regular
Joined
Jun 9, 2009
Messages
115
Fantastic, that worked and is definitely more elegant than DateValue(Now())! :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,413
Messages
5,601,534
Members
414,456
Latest member
ToniB123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top