I'm having a job trying to create a Gsheet 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][1]. It's formatted as 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 6am 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][2] 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.


  [1]: https://webapps.stackexchange.com/a/59871/85815
  [2]: https://webapps.stackexchange.com/a/22658/85815