If statement/flag on a time field

[Zara]

Hey guys, i have a date time field formated dd/mm/yyyy hh:mm:ss.

I'm trying to create a flag in the data load for inbetween our working hours, which is 09:00:00 to 17:30:00.

I feel i must be missing something really simple but i cannot for the life of me get it to work....  

Below is what i have tired...

If(Time("Call Start Time (CommsCall)")>'09:00:00'
and Time("Call Start Time (CommsCall)")<='17:30:00',1,0) as [Outside Working Hours], //Doesnt work

If(Time("Call Start Time (CommsCall)">'09:00:00'
and Time("Call Start Time (CommsCall)"<='17:30:00')),1,0) as [Outside Working Hours1], //Doesnt work

if(time("Call Start Time (CommsCall)")>'09,00,00',1,0) as [Greater Than 9], //Works

if(time("Call Start Time (CommsCall)")>='17,30,00',1,0) as [17:30 And Above],//Doesnt work, the flag starts at 17:00:00

[MarcoWedel · Accepted solution]

The issue might be that Time() as a formatting function doesn't change the numerical part of a timestamp.

So one solution could be to extract the (fractional) time part from your timestamp using the frac() function:

If(Frac([Call Start Time (CommsCall)])>='09:00:00' and Frac([Call Start Time (CommsCall)])<='17:30:00',0,1) as [Outside Working Hours]