Conditional formatting if a plannedCompletionDate hours are outside of a range (working hours)

[Rebekah_E]

Hi, I'm trying to apply conditional formatting in a View when the time on a task's Planned Completion Date falls outside a range of hours. I can't seem to isolate the time portion of the field and can't find any documentation on how to use a wildcard to use ANY dateT20:00:00. Can someone point me in the right direction?

Goal: when plannedCompletionDate time is greater than 20:00:00 and less than 11:00:00, the entire row will be red

I've tried using HOUR(plannedCompletionDate) but can't get it right -- I'm not certain it's supposed to work in a View.

Any help is greatly appreciated!

[Rafal_Bainie · Accepted solution]

Hi,

to my knowledge TIME doesn't function as standalone field, which is a shame, but what can you do...

To have a field with "time" you can do this:

CONCAT(HOUR({entryDate}),":",MINUTE({entryDate}))

if you get rid of the ":" in the middle and you are fine with 24h format you should be able to sort the hours, although adding 0 in front of 1,2,....,9 might be necessary. If you want to apply conditional formatting I would probably create separate field to evaluate if the condition is met, e.g.

IF({hours field}>2000,IF({hours field}<1100,"RED","GREEN"),"GREEN")

with these 2 fields in place you can apply conditional formatting on the first one.

Watchout! - if these fields will work as calculated fields in Workfront your results might get messed up if users recalculating expressions are in different timezones. For that reason you might want to "hard code" some of these values OR always calculate them relative to specific timezone, e.g. with help of Fusion, if you have such option.

Good luck