# Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen:

``````Sleep time  Wake time   Difference
23:30       6:00        6.50
00:00       6:00        6.00
00:30       6:00        5.50
``````

Here's what actually happens:

``````Sleep time  Wake time   Difference
23:30       6:00        -17.50
00:00       6:00        6.00
00:30       6:00        5.50
``````

The formula for the `Difference` column is

``````= (B2 - A2) * 24
``````

as per this answer. It's formatted as a number. The problem is, of course, the midnight boundary - in my head, it's clear that 23:30 means 11.30pm of Day 1, whereas 6:00 is 6 am of Day 2, but that's not clear to Gsheets. So I tried a super messy workaround where I add one to the date of the wake time, but that doesn't work either:

``````Date        Sleep time  Sleep date time     Wake date   Wake time   Wake date time      Difference
29/05/2019  23:30:00    29/05/2019 23:30:00 30/05/2019  06:00:00    30/05/2019 06:00:00 6.50
30/05/2019  00:00:00    30/05/2019 00:00:00 31/05/2019  06:00:00    31/05/2019 06:00:00 30.00
31/05/2019  00:30:00    31/05/2019 00:30:00 01/06/2019  06:00:00    01/06/2019 06:00:00 29.50
``````

It calculates correctly for the scenario where I fall asleep before midnight, but not when I fall asleep on or after midnight. I think this solution would be possible if I knew how to test whether a time is before midnight or after midnight. Doing a simple comparison does not work, as in either of these options:

``````= A1 < A2 (where A2 is '00:00:00' formatted as time)

= A1 < "00:00:00"
``````

The first one returns all FALSE whether A1 is "23:30", "00:00" or "00:30", and the second one returns all TRUE for the same parameters.

I'm sure I'm missing something obvious, but I simply don't know how to test whether a time is before midnight or after midnight in this context. Is there a way?

I'm aware of this answer which proposes formatting the time as 24:30 after midnight, which I would prefer to avoid so that it's easier to input the time, or "splitting the event in two", which I don't really understand what that means practically. If there is a boolean solution, that might be easier.

• This question was bumped few hours ago. Are you still looking for help on this? Jun 29, 2019 at 2:36

``=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "hh:mm"), ))``
``=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "hh:mm")*24, ))``
I've realised there's a better way to determine if a time is before or after midnight - just test the time component using `HOUR`. If the hour is between 0 and 6, the time can be considered to be after midnight. Otherwise it's before.
``````=AND(HOUR(C2)>=0, HOUR(C2)<=6)