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 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
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. – user127350 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

You must log in to answer this question.

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