CSV Import is incorrectly converting when there are 5 chars instead of 7 (hh:mm:ss)

[madmel1984]

If I open a csv file with the following duration data

30:00
45:00
1:00:00
1:30:00

The top numbers are incorrectly being converted to:
30:00:00
45:00:00
1:00:00
1:30:00

So zeroes are being added to the right instead of the left, changing the values. I have tried Importing and changing that column to text but it wrecks the rest of the data.

[alansidman]

Attach a sample .csv file that represents your actual file. Without actually seeing your data, it is difficult to determine what is happening and why.

[GeoffW283]

There is no way for Excel to know for sure whether 30:00 means 30 hours or 30 minutes. What Excel assumes is:
- Values from 00:01 through 23:59 are displayed and interpreted as hh:mm
- Values from 24:00 through 59:59 are displayed and interpreted as hh:mm:00.

Once the import of the CSV has been completed the information needed to "fix" Excel's assumption is lost. The only practical way I can see to achieve the madmel1984's intent is as follows: (steps are for Excel 2010, other versions may work a little differently)

1) Open a new workbook or an existing workbook into which you wish to import the data.

2) From the Data tab on the ribbon select "Get External Data" then select "From Text". Navigate to your CSV file and click "Import"

3) Within the wizard, accept the defaults for steps 1 and 2. At step 3 change the Column data format from "General" to "Text" then click "Finish".

4) Excel asks "Where do you want to put the data". Choose a location. I assume A1 for the remaining steps.

5) At this point you have a text-formatted column-A looking exactly like the the original CSV file.

6) Apply an appropriate date format to column-B (one that shows hours AND minutes AND seconds!)

7) Apply the following formula to B1 and copy down. This gives you proper dates aligned with your expected values in column-B.

=IF(LEN(A1)<7, TIMEVALUE("0:"&A1), TIMEVALUE(A1))

8) If necessary copy column-B and paste-values then delete column-A.