Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.