Subtracting time

English
#1

Thanks to @Karolus for helping with my last problem.
Now I have a new problem I have two columns with ISO formatted time, 20:42:32.000, for example, I selected the result cell and entered =D2-B2 after entering in the formula bar I get a #value! error. I did some digging to find out why this was happening. I read it had to do with my Locale settings it was English (USA), and apparently the US doesn’t use ISO number format. I changed my locale to English (Canada) I checked my cell formats and they were the same HH:MM:SS.sss format. I tried again and got the #value! error again.
Any idea what I missed?

#3

Try this format: HH:MM:SS,sss (use comma)

#4

Maybe 2 basic concepts:

  • format does not change values. If your cell has a numeric type (including date and time) you may change the format for output freely. The internal number will stay the same.
  • when you input dates the dateacceptance pattern decide, if your input is converted to an internal number or is kept as text.

Therefore: If you enter something not detected as number/date you get abtext-string (looks like a date, but is just a bin h of letters.
No change of format or locale will change this value. (“Text in columns” may do this for you as well as function VALUE() )

#5

https://help.libreoffice.org/26.8/en-US/text/scalc/01/03080000.html?DbPAR=CALC#bm_id3151384
Once you have text in your cell, no formatting nor locale setting will ever change that text.
Once you have a formattable nummber in your cell, no formatting nor locale setting will ever change that number.

#6

And this page, Calc Number text to Numeric data

#7

I tried the comma, that didn’t work.

Ryan Trullinger/Tru-Image Photography

KC0QNB Gothenburg Nebraska

#8

So many options here, I did try something I deleted the trailing :123 in two of my time cells and entered =D2-B2 then it worked right, but doing That in 200 plus cells would be a pain. If I could do that automatically for the whole column, would be great.

Ryan Trullinger/Tru-Image Photography

KC0QNB Gothenburg Nebraska

#9

Here is what I did to remove the :123 part so the subtraction would work, I don’t need .001 second accuracy, anyway. I used find and replace with this in the formula box. (:d{3})$ Now I know what to do next time, which will probably be tomorrow when I do today’s logs.

#10

Sure it does

That format code doesn’t work for me, it changes the thousandths from .000 to .3232. Format the column as HH:MM:SS.000 or maybe [HH]:MM:SS.000. As you are in Nebraska you will use the point as the decimal separator.
Then select the column and click Data > Text to Columns. For separator options tick Separated by and choose Tab (not included in the cell), click OK.
Try it out on this sample spreadsheet [English (USA)] for Column B
SubtractingTime137569.ods (10.6 KB)

#11

So you import some csv-data?
How?

It should be possible to set the right format in the import-dialogue, when you open a csv-file.
Can you share the file (or the first lines) - and obviously we don’t need the collums behind date/time.

#12

Since formatting will not (and must not) change any single value, you have to change the data. Call Select the text times, call Edit>Find&Replace and replace all points with commas within the current selection.
Instead of fixing wrongly imported data, you should import them correctly.
On the import dialog, always check “Detect special numbers” (such as dates and times).
Choose the correct import locale. For point decimals, that would be something English. If there are dates with month before day involved (“12/13/2025” or “December 13 2025”), choose “English (USA)” as import locale.

#13

neither en-US nor en-CA use decimal-comma !?

#14

There is a Danish locale “en-DK” with comma as decimal separator.
Locales_LO26.2.ods (39.3 KB)

#15

maybe … but @earthshaker lives in Nebraska!

#16

We have all the data needed for the experiments.

  1. Create a test file, for example, test1.csv, with the following content:
20:42:32.000
  1. Open this CSV file with the following parameters:
    2026-09-12 142848
    2026-09-12 142848471×657 22.8 KB

Pay attention to the Locale and Detect special numbers options.

Cell A1 contains a number (a date), not text.

1 Like
#17

OK,OK, with English locale, point is the right decimal separator. I was confused by @schiavinatto’s answer. You should check “Detect special numbers” in the import dialog (that setting will be remembered next time).
Fixing wrongly imported data might work like this, if the times are all in one column:

  1. Select the column with time data.
  2. Format the cells in some English time format.
  3. Data>Text to columns… no separator… [OK]

This will effectively re-enter all the data in that column.

#18

Yes, it is a CSV. Here are a few lines.

ReceptionStart ReceptionEnd
2026-09-11 09:00:02:291 2026-09-11 09:00:10:399
2026-09-11 10:00:02:938 2026-09-11 10:00:10:318
2026-09-11 10:50:11:945 2026-09-11 10:50:25:413
2026-09-11 10:56:21:095 2026-09-11 10:56:32:138
#19

Let’s clarify:

What, after all, separates the fractional part of the seconds?

#20

Maybe an alternative solution with helpers :thinking:

  1. To extract: CD = MID(<start or end>, 12, 8)
  2. Duration column E
  • format = HH:MM:SS
  • = Di - Ci

Durations

#21

It is a colon and I can’t find and replace one colon without changing all of them.

Ryan Trullinger/Tru-Image Photography

KC0QNB Gothenburg Nebraska

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.