Help with IF/Then statement for values between 2 numbers

K.Bisnett

I manage a sheet where we track Inspection reports and compliance statistics for meeting 24 hour deadlines. I calculate regular submission turn-around time by date submitted - report day. Problem is, that doesn't work for my night shift inspectors because their shift ends on the next day, so they always appear a day behind.

I want to find a formula that identifies the night shift inspections by shift start time, and return a -1 for times between 6:00 PM and Midnight, and a 0 for all other times in a separate column. Then I'll subtract regular Submission from Night Shift for a more accurate value.

This is the formula I'm using in the Night shift column

=IF(AND([Shift Start Time]@row >= "18:00:00", [Shift Start Time]@row < "00:00:00") -1,-0))

I've tried it with and without the " marks, with and without the "equal to" by the greater and less than symbols. 100 variations.

The shift hours are imported from our report data base and are in the 24 hour clock with seconds- I'm hoping that I don't have to modify that entire column- we have over 10,000 reports.

Any help is greatly appreciated!

Eric Law

@K.Bisnett You're missing a comma which might affect it, also your time for midnight is wrong as you would get an error for less than 00:00:00.

=IF(AND([Shift Start Time]@row >= "18:00:00", [Shift Start Time]@row < "23:59:59"), -1,-0)

In reality, you don't need the second part of the Shift Start Time since anything greater than 00:00:00.

=IF([Shift Start Time]@row >= "18:00:00", -1,-0)