If time between to periods

[gmazza76]

Good afternoon,

I am trying to tell if a time entered on my information falls between a set period to work out if the shift (day/night shift) in my formula.

=if(and(time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))>=TIME(20,00,00),
time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))<=TIME(08,00,00)),"LateShift","DayShift")

I am a bit stuck as I am unsure how to move forward with this.

thanks in advance
Gavin

 

[sandy666]

1. Power Query is case sensitive
2. M style is NOT a worksheet formula style
3. are you sure your post is in a proper forum?

 

[etaf]

I suspect this is wrong
and(time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))>=TIME(20,00,00),
time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))<=TIME(08,00,00))
AS the cell cannot be > 20:00 AND also less than 08:00
TRY using
OR
OR(time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))>=TIME(20,00,00),
time(hour(Data[Time]),minute(Data[Time]),SECOND(Data[Time]))<=TIME(08,00,00))

 

[gmazza76]

thanks @etaf I have amended the from and to time and it has worked using the "IF(AND"

 

[etaf]

you are welcome, BUT surprised the AND works as the TIME() is a number between 0 and 1
And 20:00 and above is in general format 0.833333 to 1
And 08:00 and below is 0 to 0.3333333
So > 0.8333 AND < 0.3333 - is not possible , UNLESS you are including the actual DATE itself