Conditional formatting if a plannedCompletionDate hours are outside of a range (working hours) | Community
Skip to main content
R
Rebekah_E
February 16, 2023
Solved

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

  • February 16, 2023
  • 1 reply
  • 609 views

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!


This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Rafal_Bainie

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

1 reply

Rafal_Bainie
Community Advisor
Rafal_BainieCommunity AdvisorAccepted solution
Community Advisor
February 17, 2023

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

Terms & ConditionsAccessibility statement

Loading footer...