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 often copy + paste data from different data sources into Excel. One field is an hour + minute field (formatted hh:mm).

When I paste the data into Excel hour values of 24 and higher get converted correctly while values of less than 24 get converted into minutes and seconds (see sample data below).

So it appears that Excel is doing some automated conditional formatting based on if the data could be interpreted as time.

Does anyone know how to fix this?

| 22:40    | ←this should be 22:40:00
| 23:25    | ←this should be 23:25:00
| 23:55    | ←this should be 23:55:00
| 23:59    | ←this should be 23:59:00
| 24:05:00 | ←ok
| 24:29:00 | ←ok 
| 24:35:00 | ←ok
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Yes. It's because one is converted into Time and one into Date, and different formats.

For changing format:

  1. Right click on the column header.
  2. Select "Format Cells...".
  3. Select "Time", from the Category list of "Number" tab.
  4. From the Type list, select the item similar to this "37:30:55".
  5. Click "OK".
share|improve this answer
    
That worked, Thanks. Is there a way to set a default type for these kinds of values so I don't have to do it manually all the time? –  Andre May 8 '12 at 14:33
    
I don't see any option in the Text Import Wizard for the format, only for the type. The easiest way I can think right now is to create a template with the columns pre-formated as needed, and use this template when creating a new document. –  Radoo May 8 '12 at 14:38
    
Thanks. I really wished that MS actually made things simpler. Copying and pasting data into Excel is the bread and butter of the company I work for and everybody has formatting issues. –  Andre May 8 '12 at 14:58
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.