Conditional Formatting based on Time for multiple conditions

[vtruxton]

Hello Everyone,

I have a table that has columns Name, Time (formated as HH:MM: SS AM/PM) and a KPILight using Unichar(11044). My goal is to have the KPI Light change color based on the following:

If Time is +- 15 min from 3:00 PM turn KPI Light Green OR if Time is +- 15 min from 4:00 AM turn KPI Light Green, else turn KPI Light Red.

I've been playing around with different IF formulas in the formula bar but haven't been able to get this working. Any guidance would be greatly appreciated.

[v-cherch-msft · Accepted solution]

Hi @vtruxton 

You may try to add a time column in query editor first.For example:

Then create a rule measure like below and use it for KPILigtht column with conditional formatting.

Rule =
IF (
    ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 15, 0, 0 ), MINUTE ) ) <= 15
        || ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 16, 0, 0 ), MINUTE ) ) <= 15,
    1,
    2
)

Regards,