Excel 2007 : Converting/formatting to minutes and seconds

**Maristar** · 10-14-2009, 01:23 AM

Hi,
I have a whole lot of values that I need to analyse, which are almost like stopwatch times - lengths of video clips. They are all quite short, and therefore I need a mm:ss formatting. However, whenever I do this in Excel 2007, it formats the numbers to 24 hour date time - so while it still looks like "01:03" in the cell, in the formula bar it will say "12:01:03 AM". This wouldn't matter if I just wanted the cells to look right, but I need to calculate means, standard deviations etc. I originally had it just as mm.ss, but realised that the different systems (base 60 verses base 100) would create problems with my analysis.
I have converted these times to seconds, but would much prefer to work with a mm:ss formatting as it's clearer.
Any help would be much appreciated.
Thanks,
Marissa

**DonkeyOte** · 10-14-2009, 04:38 AM

In terms of data entry XL's default is hours:minutes so entering 01:03 is 1 hour and 3 minutes, for 1 minute and 3 seconds you must enter 0:1:03 (format to mm:ss accordingly)

For speed of entry you can enter all times as before (ie hour & minutes), once data entry is complete enter 60 into a blank cell, copy it and paste special over your time values using Operation: Divide.
This will then convert the values from hours & minutes to minutes and seconds (alter format accordingly).

**Maristar** · 10-14-2009, 05:00 AM

Thank you for your reply.

I did understand this - however, when I format this in Excel 2007, it formats into date/time, not just hours/minutes/seconds.

If I format them in this way, will the analysis be right? or will it be based on time (i.e. 24h time).

Thanks again,

Marissa

**DonkeyOte** · 10-14-2009, 05:09 AM

If you enter a minute:second time value like

30:20

Given XL sees this as 30 hours and 20 minutes it translates this as being:
1 day, 6 hours and 20 minutes and displays accordingly.

To reiterate my point regards resolution - simply divide your existing values by 60 (via Paste Special: Divide) to move from an hours based values to a minute based values.

Once your values have been corrected by means of the above you can then in turn simply adjust the cell formats as per your preference:

hh:mm:ss

or

[mm]:ss

if you prefer to see the minuntes over 60 in cumulative fashion (eg 70:23 for 1 hour and 13 minutes)