Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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