How to put the conditional formatting for a date field in time format?

Question & Answer
, ,
1

I have a query like I want to put a conditional formatting in a date field with time format like if the Job Start Date is 13-Jan-2023 06:13:32 AM then I want the time format condition like if the Job Start Date is less than or equals to 06:00 AM then Green if it is greater than 06:00 AM and equals to 07:00 AM then Amber or if it is greater than 07:00 AM then Red

2

Are you doing this in SQL or quicksight?

You can use extract to get the value of the hour in quicksight and SQL.

ifelse(extract(‘HH’,{date_field})=7,‘Amber’,extract(‘HH’,{date_field})>7,‘Red’,‘No color’)

3

Hi @Max I am doing it in QuickSight.

4

Hi @Max can we put the condition as AM & PM for Date Time

5

No, but you can add 12 to it.

7pm = 7am +12.

6

OK @Max let me try once.

7

Hi @Max I am not able to achieve my result something gap in my formula.

My requirement is
if the Date is like 07-Mar-2023 07:00:00 AM — Then color is ‘Amber’
if the Date is like 07-Mar-2023 06:00:00 to 07-Mar-2023 06:59:00 – Then ‘Green’
if the Date is greater than 07:00:00 then ‘Red’

8

Hello @Biswajit_1993 , posting here the same response as in the other topic for coherence.

  1. to use the extract function on the date you have to get just the hour ‘HH’ portion of it

  2. then use conditional formatting for the coloring.

  3. Create a calculated field (hour) using extract to get the hour from the date field:

extract('HH', {your_date_field_here})
  1. Use the conditional formatting with the rules depicted below in the hour field:

image
image1533×1135 84.9 KB

Hope it helps, please mark this solution as solved if that’s the case also to help other members of the community., otherwise let us know.

Happy dashboarding!

9

Thanks @EnriqueS for your solutions but in my datasets the date format is 12 hrs date format and the date is like AM & PM please find below some example then in this solution what will be my conditions because 7 AM I can take as ‘Amber’ color but 7 PM should be ‘Red’ but it is showing ‘Amber’

Mar 3 2023, 7 PM
Mar 3 2023, 8 PM
Mar 3 2023, 7 AM
Mar 4 2023, 7 AM