How to return a value based on a range of Times

I’m trying to return a value of what shift a tech starts his day based on when he inputs his starting time.

If they start anytime between 4 am and Noon that is shift 1

If they start between 12:01 and 8PM that is shift 2

If they start between 8:01 PM and 3:59 am that is shift 3

 

Right now I have the below formula in, and it seems to work if I enter a time in for anything for shift 1 or 2, but for some reason I can’t get shift 3 to work.

=IFS((M10<=TIME(12,0,0)),1,(M10<=TIME(20,0,0)),2,(M10<=TIME(4,0,0)),3)

By: Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *