hh:mm to mm:ss

[plum]

Hi There,

I have been given data which has time in hh:mm format displaying the data as 01:12 for example.

The problem is, that this is supposed to be one minute 12 seconds, however excel has automatically converted to hh:mm.

I've tried formatting the cell to be mm:ss but it generates a random amount of minutes. Is there any way someone can help without me having to reenter the data, as its hundreds of rows!

I need help on this quick, like tonight!!!!

[shg]

Welcome to the forum. I've edited your thread title to be a little less breathless. This would be a good time to take a few minutes to read the forum rules.

How about posting a workbook and explaining in context?

[Paul]

Hi Plum, welcome to the forum.

Assuming your list of incorrect times start in A1. In B1 use:

=A1/60

and format that cell as mm:ss. This should give you the correct results. You can then copy column B and PasteSpecial over itself and then delete column A.

[rwgrietveld]

Time is a value between 0 and 1. So every second is represented by
1 / 24 / 60 / 60, so if you device your value by 60 ...

make an additional column. Enter the formula =A1/60

Now copy/paste special value back into column A

[plum]

Hi thanks,

Slightly panicked!

Some sample data attached. Its from a Time in Motion study. The times you see displayed are supposed to be in minutes and seconds, not hours and minutes.

For example cell A1 shows 01:10, this is supposed to be one minute 10 seconds. However on clicking the cell, the formula bar displays 01:10:00 (hh:mm:ss).

I was using the forumula =AVERAGE(REFA:REFB) to get the average time per column, which was displaying some random results before I did a double check on the data (bad move on my part).

Can you please help me convert this from 01:10:00 to 00:01:10!

Hope this is more concise and less exhausting!

[plum]

guys thank you so much for your help! much appreciated. phew! will be visiting this forum more often!