Put 22:00 in A1 and the formula still does not work. What I am looking for is to determine if time in cell A1 falls between the times in B1 & C1. Easy until you bring in 3rd shift start times such as 23:00 and 06:00. I been banging my head on this for sometime with no satisfactory results.
Yes, your problem is that time is denoted as a percentage of the day. ie 00:00 is 0%, 23:00 is actually 95.8...% and 06:00 is 25%. Therefore, whilst midnight may be between 23:00 and 06:00 it is not as far as percentages of a day are concerned.
This is because you are trying to measure times between two independent days which are not linked.
There are a couple of ways around this depending on what you actually want to do.
If it is a simple matter of being between 23:00 and 06:00 the following day you should treat the times as say a 48 hour clock. ie 06:00 would be in the second day and so actually be 30:00 and midnight would be 24:00. If you format the cells correctly they will still show the 24 hour clock but, in the case of the 06:00 time it will be 125%.
If it goes beyond the simple approach of two days maybe you should utilise the full "date/time" format by formatting the cells for dates and times. eg dd/mm/yyyy hh:mm