Excel formatting?

Question

I have an excel sheet with times in the format of hours:minutes:seconds (h:mm:ss). My problem is that I have been entering time with just minutes and seconds

Example
1 min and 30 seconds was entered as: 1:30

The problem with this is that the formatting changes that to 1 hour and 30 minutes.

entered as 1:30 
changes to 1:30:00
displays as 1:30

I have too much data at this point to manually change the data, so I was wondering if there is a formula of some kind that I can use to change the data so that if I have 1:30 it gets formatted as mm:ss

Example
Already existing data entered as 1:30 and formatted as 1:30:00
will be changed to formatting 0:01:30
1:30 = 0:01:30
1:30:00 = 1:30:00

I have already tried changing the formatting to mm:ss and that does not work. There is so much data already that going in manually to change the formatting would take hours and I am going to be averaging the times and doing other calculations that require the formatting to be correct.

Answer 1

Time values in XL are floating point numbers in fractions of one day. So 1:30 minutes or 90 seconds would be - formatted as a number - 90 / 86400 = 0.0010416666. You can check that with simply changing the format on a cell.
To "convert" mm:ss values into hh:mm:ss values, divide the cell contents by 86400. Put the number 86400 into a cell, copy it, and past special, value, divide by, into all cells with time values. Format those cells with a suitable time format.
Of course, this replaces the unknown hour value with zero - if you need a particular hour, add this to each cell: h*3600/86400.

Answer 2

If A1 contains the cell that you want to transform, then in B1, enter

=TIME(,HOUR(A1),MINUTE(A1))

and copy downwards. That will transform the input that's been parsed in error to minutes and seconds. Note well the leading comma in my formula.

This works because there are the same number of minutes in an hour as there are seconds in a minute, and there are fewer hours in a day than there are minutes in an hour.

Once you've done this, copy the new range and paste it as values, and you're done.