Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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:

05:00:27
01:27:02
07:29:57
06:22:01

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:

'05:00:27
'01:27:02
'07:29:57
'06:22:01

Why is that? How can I prevent it?

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .