Skip to main content
Bumped by Community user
added 5 characters in body; edited tags
Source Link
user0
  • 27.1k
  • 10
  • 53
  • 78

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

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

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 6am6 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:

Boolean test of whether a time is before or after midnight in Gsheets?

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:

as per this answer. 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:

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:

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:

Source Link
Lou
  • 225
  • 2
  • 13

Boolean test of whether a time is before or after midnight in Gsheets?

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. 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 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.