Thread: Checking 1 time against 3 time ranges

Hello,
I'm trying to do a formula which looks at a single time, checks it against 3 time ranges, and apply the appropriate name for whatever the time falls under which range.

Example:
I have a column with varying times 00:01-23:59. Say C2 is 00:01. I want to check that time to the following and reply with corresponding value.

First 07:00 16:00
Second 16:01 00:00
Third 00:01 06:59

I have a start, which works for a single check:
=IF(PRODUCT({"00:00","07:00"}-C2)<0,"Third")

Getting closer, but the shift time (First,second,third) is not applying properly..

=IF(PRODUCT({"00:01","06:59"}-C2)<0,"Third",IF(PRODUCT({"16:01","00:00"}-C2)<0,"Second",IF(PRODUCT({"07:00","16:00"}-C2)<0,"First")))

C2 = 00:01 ... is applying "Second" for some reason. ?????

clawton8,

Its not pretty, but it should work:

=IF(HOUR(C2)+MINUTE(C2)/24>=16+1/24,"Second",IF(HOUR(C2)+MINUTE(C2)/24>=7,"First",IF(HOUR(C2)+MINUTE(C2)/24>=1/24,"Third","Second")))

Hope that helps,
~tigeravatar

Hi, that looks to have worked. Thanks!

Can you break this down for me so I can understand?

I don't get the /24>=16+1/24 or just /24>7

clawton8,

Excel doesn't read dates or times like a human does. It reads them according to their serial number. Date serial number 0 is January 0, 1900, 12:00:00 AM. Each whole number in the serial number is a day. So serial number 1 is January 1, 1900, 12:00:00 am

Excel can read the hour or minute value with the formula functions =Hour() and = Minute(). In order to see if something is x hours and 1 minute, you need to compare it to x + 1/24, so =Hour()+Minute()/24=x+1/24 as excel converts the date serial number to a real number that can be checked against. The formula I posted uses that logic to check against your time ranges and return the desired results.

~tigeravatar