Excel formula for between time frames-CodePudding

not sure if stack also accepts/allows general excel questions but figured I'd ask anyway. Looking for help with the excel formula that checks the time in one column and returns the shift (day, night or overnight) in another column. I have the night and overnight shifts outputting correctly but for some reason the day shift does not?

Here are the two excel formulas:

=IF(AND(HOUR(Table8[@Time]) >7, HOUR(Table8[@Time] <15)),"1st shift")

=IFS(AND(HOUR([@Time]) >= 7, HOUR([@Time] <= 15)), "1st shift", AND(HOUR([@Time]) >= 15, HOUR([@Time]) <= 23), "2nd shift", OR(HOUR([@Time]) >= 23, HOUR([@Time]) <= 7), "3rd shift")

CodePudding user response：

you put the bracket at the wrong place for the second check for the 1st shift

This is correct: =IFS(AND(HOUR([@Time]) >= 7, HOUR([@Time]) <= 15), "1st shift", AND(HOUR([@Time]) >= 15, HOUR([@Time]) <= 23), "2nd shift", OR(HOUR([@Time]) >= 23, HOUR([@Time]) <= 7), "3rd shift")

HOUR([@Time] <= 15) is wrong

CodePudding user response：

The parenthesis is the issue here

Check it out like this

=IFS(AND(HOUR([@Time]) >= 7, HOUR([@Time]) <= 15), "1st shift", AND(HOUR([@Time]) >= 15, HOUR([@Time]) <= 23), "2nd shift", OR(HOUR([@Time]) >= 23, HOUR([@Time]) <= 7), "3rd Shift")