Shift Categories Based on Date and Time

[markefrody]

Hi,

I have a list of date and time per item. I need to add a calculated column wherein if a time falls between the below ranges they should be classified as follows:
A.) 4:00 AM - 3:59 PM

B,) 4:00 PM - 3:59 AM

Would you be able to help me with a DAX code for this calculated field? Below is an example of how it should like (Column D).


I am using Direct Query for the data.

Any help is greatly appreciated. Thanks!

Best regards, 
Mark V

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula will work

=if(and(mod(Data[Creation date],1)>=time(4,0,0),mod(Data[Creation date],1)<=time(15,59,0)),"4 AM - 3:59 PM","4 PM - 3:59 AM")

Hope this helps.

[markefrody]

Thanks @Ashish_Mathur! Works perfectly!

[Ashish_Mathur]

You are welcome.