Auto-Numbered Column - Conditional Formatting

Kylie D'Costa

Hi SS Users,

 

I have a system populated column which automattically updates the date and time a row is amended.

 

I am wondering if there is a way to conditionally format this column?

 

Ie - if the last update was today (any time) go green. All rows with a latest update of anytime before go red.

 

I know how to use conditional formatting, but can't get it to work because the autopopulate column has the date AND time. 

 

Any suggestions??

Ta

Kylie

J. Craig Williams

Kylie,

 

This appears to be a bug.

 

To work-around this, create a new Date type column and capture the date in there

=[Modified]23

 

for row 23.

Use that column in your conditional formatting.

 

FIRST BUG:

When I set the condition to "Modified is a date", I get positive results for all rows.

 

but I get no positive results for "in the past", "is today", or "in the future".

Last I checked, if it is a date, it has to be one of those three, unless you start dealing with theories of non-linear time, then it might be all three. I digress.

 

SECOND BUG ALERT:

The date displayed in the [Modified] column may NOT match the date displayed in your new column - this is due to the fact that the underlying value is in UTC time and your display time formula will be grabbing the actual value, not the value you see.

Sort of.

 

A Modified display value for me (east coast USA)

 

03/06/17 8:40 PM

 

=Modified23

will display

 

03/07/17

 

and

 

=Modified23 + ""

 

will display (text)

 

03/06/17 8:40 PM

 

Hope this helps.

 

Craig

 

Frank Mazzella

Wow, thanks Craig.  Thinking about the UTC issue, I added a rule to apply the format to rows that have been updated in the future, and that worked.

Kylie D'Costa

Thanks Craig - will give it a go!

 

Cheers

Kylie