Label status based on Time

[atjt217]

Hello, 

Would like to ask your help please? I have a table list of Appointment Dates and Times and I want to put a status to either Regular hours or After hours. It will be After hours if the appointment time is before 8am and on or after 5pm. Here is an example of what im trying to do: 

AppointmentId	Date	Time	Status
1044615	9/26/2021	12:30 PM	Regular Hours
1051666	9/27/2021	5:00 PM	After Hours
1054426	9/30/2021	3:05 PM	Regular Hours
1054427	9/30/2021	3:05 AM	After Hours
1055334	9/27/2021	10:00 AM	Regular Hours
1067589	9/27/2021	8:15 AM	Regular Hours
1074673	10/2/2021	9:50 AM	Regular Hours
1085412	9/26/2021	1:45 PM	Regular Hours
1090280	10/1/2021	7:45 AM	After Hours
1090293	9/29/2021	2:00 PM	Regular Hours
1091191	10/1/2021	2:30 PM	Regular Hours
1099277	9/28/2021	5:00 PM	After Hours
1099290	9/29/2021	10:20 AM	Regular Hours
1099296	9/29/2021	10:18 AM	Regular Hours

Please note im using direct query. I appreciate your advice. Thank you!

[smpa01]

@atjt217  not sure, but it should not yield different result for DQ

[smpa01]

@atjt217  calculated column

Column = if(TIME(08,00,00)<='Fact'[Time]&&'Fact'[Time]<=TIME(17,00,00),"reg","after")

and in case , if this needs to be a measure

Measure = if(TIME(08,00,00)<=CALCULATE(MAX('Fact'[Time]))&&CALCULATE(MAX('Fact'[Time]))<=TIME(17,00,00),"reg","after")

[atjt217]

Hi, Thank you for responding back.

I tried both but its not working. The formula is used was: 

Status = if(TIME(08,00,00)<=vu_Bi_UnableToFill_2019ToCurrent[Time] && vu_Bi_UnableToFill_2019ToCurrent[Time]<=TIME(17,00,00),"reg","after")

Is there anything i missed? Please let me know

[v-stephen-msft]

Hi @atjt217 ,

Try to remove the equal sign

Column = if(TIME(08,00,00)<vu_Bi_UnableToFill_2019ToCurrent[Time] && vu_Bi_UnableToFill_2019ToCurrent[Time]<TIME(17,00,00),"reg","after")

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[atjt217 · Accepted solution]

I found a work around on this issue and wanted to share to everyone. Instead of using TIME i extracted the Hours on the Date column using Power Query and used this calculated column formula:

Appointment Status = IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 8, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 9, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 10, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 11, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 12, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 13, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 14, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 15, "Regular Hours",

IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 16, "Regular Hours",

"After Hours")))))))))

[v-stephen-msft]

Hi @atjt217 ,

Glad you can find a solution, please mark your reply as an answer, more people will benefit.

Best Regards,

Stephen Tao

[smpa01]

@atjt217  this is what I see. it is the same measure as previous one.

[atjt217]

Could it has somthing to do with being in Direct query?