How to subtract figures within a cell?

ManyWords · May 3, 2025, 2:51pm

In each cell of a column I have a time range, e.g., [02.30-08.30]. I want to create a new column which shows the total hours within that range, so 8.30-2.30 = 6.

Is there a way for me to do this without having to manually do every single calculation?

There are also some cases where it is [23.30-04.00] for example, in which case I cannot do a simple subtraction do get the figure I want of 4.5.

It would be very helpful if I can get a solution to the former case. Even better if I can get a solution to the latter, more complicated, case.

PKG · May 3, 2025, 3:02pm

@ManyWords ,
16334 columns are not enough for them that they have to squeeze everything into one cell?

Hrbrgr · May 3, 2025, 3:05pm

https://web.archive.org/web/20210507043509/http://www.ooowiki.de/CalcFunktionenDatum(2f)RechnenMitDatumUndZeit.html

sokol92 · May 3, 2025, 4:03pm

=MOD(TIMEVALUE(SUBSTITUTE(MID(A1; 7; 5); ".";":")) - TIMEVALUE(SUBSTITUTE(MID(A1; 1; 5); ".";":"));1)

SubtractDates.ods (9.2 KB)

Lupp · May 4, 2025, 4:47pm

A teacherly bloated and opinionated solution:
disask121635TimeIntervalsAgain.ods (40.2 KB)

joshua4 · May 4, 2025, 6:47pm

I might expand on @sokol92 to avoid worry about the leading zeros and to put the result in a decimal format, which seems to be the OQ’s desire:

=ROUND((TIMEVALUE(RIGHT(SUBSTITUTE(A1,".",":"),LEN(A1)-FIND("-",A1)))-TIMEVALUE(LEFT(SUBSTITUTE(A1,".",":"),FIND("-",A1)-1)))*24,2)

koyotak · May 5, 2025, 6:44am

extremely cryptic the ‘=Regex()’ function

ManyWords · August 28, 2025, 8:18pm

Thanks everyone for the help. I’m so sorry for disappearing after posting this… I’m really bad at Calc and struggled to make sense of the responses and apply the solutions.

Now I’m more determined.

Let me explain the data and what I’m trying to achieve. I’ve been recording sleep times as I’m monitoring insomnia using a health-focused spreadsheet. I have two columns: A = Date. B = Sleep Times. I have over 500 rows for over 500 days I’ve been recording this. Now I want to see the hours spent each night in-bed/sleeping.

@sokol92 solution seems good. @joshua4 is right that it’d be good to remove all the zeroes, but I tried copy/paste, replacing sokol’s B1 cell, but the output didn’t make sense: [144:00:00].

I don’t know how to take Sokol’s solution and apply throughout the entire column. Can somebody assist me?

And I think @pkg and @lupp are right in saying I’ve formatted this terribly. I intend on changing it to something much more simple going forward, so that each variable has its own column.

koyotak · May 4, 2025, 4:08pm

I recognize a character block consisting of [number-letter-number-letter-number-letter-number]. The number is ambiguous: time 00:00-11:59:59 or 00:00-23:59:59 or decimal. The letter is ambiguous: decimal point “0.0” or comma “0,0”, as well as from-to “-” or minus “–”.

another algorithm with extraction, substitution, convertion of individual characters, my solution with fade out columns and step-by-step elaboration
SubtractDates#1_060837.ods (20.1 KB)