Calc changing 3007:1000 to 3023:40:00... why?

I have a bunch of fields (all numbers) I need to copy/paste into calc in the format xxxx:yyyy. However, if I paste 3007:1000 into calc, it changes it to 3023:40:00.

Why is this and how can I stop it?

Calc thinks it’s a number in the form HH:MM and it’s converting it to HH:MM:SS (1000 minutes equals 16 hours 40 minutes). The problem with this is that even if you clear the number format it will be changed to 125.9861111.

I didn’t find a “paste as string” or anything similar so your best bet is this:

Prepend a single-quote mark (’) to each value before pasting [*] to force Calc to interpret your values as strings.

The problem is I have hundreds of these values to insert. There has to be a way to turn this off globally no?

I don’t think so, but there are ways to work around it:

  • Save them to a CSV and load them from there so you get the Text Import dialog, where you can deselect “Detect special numbers”.
  • Paste it to a plain-text editor, add an extra row and paste all back. Pasting more than one row also makes LibO show the Text Import dialog.

It is converted to time value.

When there are more than one value to paste you can use Menu/Edit/Paste special [Shift+Ctrl+V], click on the column head and select text as type, but it doesn’t work with only one value.

Please report the bug: Create a bug report