2

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? – Rubén Jun 29 at 2:36
1

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "hh:mm"), ))

0


=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "hh:mm")*24, ))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.