I have some data in calc like so:


I select the cells, and right click 'format cells', and choose Time -> 876613:37:46. When I do this, it adds an apostrophe to the beginning of each cell, so the data ends up like this:


Why is that? How can I prevent it?

  • 2
    The problem is that if your cell contain "05:00:27", they are already formatted. Without formatting, the content would be "0,208645833333333" in case of "05:00:27". So, the answer depends on the "real" content of you cell. To check this, remove any "direct formatting" using Menu "Format" -> "Clear direct formatting", or using CTRL+M. – tohuwawohu Jul 5 '12 at 9:14

Very often the time value is presented as a string ie. appears as '01:00:00 when formatted as time.

There are two solutions to this:

  1. To delete the single quote. This is extremely tedious if there are more values

  2. To create another cell which uses a formula to convert to timevalue. If eg A1 contains '01:00:00 then create another cell with =TIMEVALUE(A1), the formula can be dragged along to easily convert multiple values

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.