how do I paste in a time so that it is not formatted as text

010Robin · October 31, 2020, 11:01am

I frequently paste data into calc that includes fields such as 00:01:30, 12:30:00 etc. These are recognized as text instead of times. They are left justified instead of right justified. I want them to be recognizes as times.
If I select the column and change the format to time, it converts all the entries as text, with a '. This isn’t what I want either.
How can I set it so that times are automatically recognized as such, or how can I easily format the column as time, please?

igorlius · October 31, 2020, 11:10am

Hi,
you have to change/edit the cells Format for this.

Select the Cells (or Columns) where you want to place the timestamps
Right Click into the Selection and in the Submenu select Format Cells ... or via the Menu: Format > Cells ...

Here you can set/describe the way your cells should be formated.

See: Category > Time

Hope it helps.

erAck · October 31, 2020, 11:48am

The cell format does not have be changed to Time before pasting. Only if the cell format was Text before then it needs to be changed to some numeric format, including Time, but General works as well.

anon73440385 · October 31, 2020, 11:21am

Hello,

using Right click -> Paste Special ... -> Unformatted Text should do the job, though Unformatted Text sounds a bit counter-intuitive, since you want it as a number and not text. But telling LibreOffice to paste unformatted text makes the number recognition fully active, while not doing so may paste format information as well (and hence turns to text)

To change the format (if already entered and having a ' in front of the time) perform:

Select the times (or dates)
Data -> Text to Columns...
Click OK (without making any settings)

Note: This procedure only works that way, if a ' automatically appeared after having entered the time or date. The '-character indicates that LibreOffice “knows” the data could be a number as well.

Hope that helps.

ledruide · February 20, 2023, 10:35pm

For me (on Ubuntu 22.04) this required all of the above steps: (1) paste the copied data as unformatted text; (2) select the pasted text and change the format to Time; (3) convert the pasted/selected data to values with Data/Text to Columns.

Villeroy · February 20, 2023, 10:40pm

Simply tick option “Detect special numbers”. That’s it.
Conversion of wrongly imported text can be desastrous.