Selected Answer

The TIMEVALUE function converts a text string into a number. In the case of TIMEVALUE("22:00") the number is 0.916666667. For for 6 AM it's 0.25. Calculate 1 / 24 * 6, meaning [1 day] / [24 hours] * [6 o'clock]. If you wish to indicate tomorrow's 6 AM you must add 1 day. Therefore 1.25 will be tomorrow's 6 AM. You could create such a number with the formula = TIMEVALUE("6:00") + 1

=TODAY() will give you a number like 42964. =NOW() will return a number like 42964.80554. Accordingly, todays's 10 PM would be 42964.91666667 and tomorrow's 6AM will be 42965.25. If you actually have such numbers everything is very easy. More than half of your problem comes form having text which looks like numbers instead of numbers which are displayed like text.

So, learn about number formats. In a new blank worksheet all cells are formatted as "Gneral". In a cell with this format, enter 42964.91666667. You will see the number. Now change the number format (Format > Cells > Numjber > Custom) to "dddd" (without quotation marks) and your cell will display "Thursday". Format it as "dd mmm" (without quotation marks) and you will see "17 Aug". Set the format as "hh:mm" (without quotation marks) and you will see "22:00". Let the format be "dd/mm/yyyy hh:mm" (without quotation marks) and the cell will show "17/08/2017 22:00"

The same system works the other way around, too. In a cell formatted with "hh:mm" enter "18/8/17 6:00" (no quotation marks) and the cell will display "06:00". The date will be hidden, but the cell will hold the complete number. You can format it as "General" and see the actual number. In this way tomorrow's 6 AM can be made different from today's 6 AM although both cells show the same "06:00".

You can't read these cells with TIMEVALUE or DATEVALUE. Instead you treat them like numbers, for example,

`=IF(AND(Y2 >(TODAY() + (1/24*22)),Y2<(TODAY()+1+(1/24*6))),1,0)`

This formula requires Y2 to hold a true Date/Time value like =Now()

Based upon your revised workbook I suggest another approach. Try this formula in B3.

`=COUNTIFS(Start,"<=" & MOD($A3,1),End,">=" & MOD($A3,1))`

Before this formula can work you should do two thigs, however. First, create two named ranges. F4:F8 = "Start" and G4:G8 = "End". This serves to make your formula more readable and changes easier to manage. Second, instead of entering 6:00 in G8 enter 30:00. That changes the cell's value from 0.25 to 1.25 with the implication that it is the next day's time.