Ask Your Question

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

asked 2020-10-31 12:01:36 +0100

010Robin gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-10-31 12:21:16 +0100

Opaque gravatar image

updated 2020-10-31 12:24:48 +0100


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.

edit flag offensive delete link more

answered 2020-10-31 12:10:12 +0100

igorlius gravatar image

updated 2020-10-31 12:15:00 +0100

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

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

image description

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

See: Category > Time

Hope it helps.

edit flag offensive delete link more


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.

erAck gravatar imageerAck ( 2020-10-31 12:48:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-31 12:01:36 +0100

Seen: 25 times

Last updated: Oct 31 '20