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.