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

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)