Excel: Prevent mm:ss format data being interpreted as HH:mm

1
votes

I have a column of data in the form mm:ss (minutes, seconds) which I want to copy into Excel, but Excel interprets this as HH:mm which means any times over 24 minutes are represented as DD/MM/YYYY HH:mm in Time format.

Presumably there's an easy way to tell Excel the correct format of the data you're importing but I don't know what it is. Can anyone help?

2
exceltimeformat
quick fix, not correct, but quick, format the cells [hh]:mm:ss Longer fix is to have the data that being imported changed to add the 00: for hours. After that it is a matter of writing code that divides the time by 60 to get the correct time reference. – Scott Craner

2 Answers

1
votes
  1. Select the column / cells in question
  2. Right-click on it and then click on "Format cells" or "Format"
  3. Choose "Text" so that the content is not formatted and displayed as it is

More details:

Hope it helps :)

0
votes

There seems to be a simple solution which works for me: 1. Paste in data. Excel will automatically assume it's hh:mm:ss 2. Divide each cell by 60 e.g. B1=(A1/60), B2=(A2/60) etc. Hours become minutes, minutes become seconds.

NB: this won't work if you have anything other than 0 in the milliseconds section, because obviously there are 1000 milliseconds in a second, not 60.