Entering/Adding Time in EXCEL

**ctrapper** · 07-08-2013, 11:59 AM

I just entered a bunch of Time in EXCEL 2010, and I think I used the wrong formatting option. Right now I have 3:47:00 in my Column, which is supposed to be in minutes and seconds. So my question is, can I convert the existing data into the right format so when I add it up, it will add up to the correct amount of hours? I started re-entered the time using leading zero's but it's going to take me a while. Wanted to see if I can use what I already have. If I have to retype, is there a format I can use where I won't have to type in the leading zeros?

**Special-K** · 07-08-2013, 12:37 PM

3:47:00 is 3hrs 47 mins0 seconds

0:3:47 is 3mins 47 secs.
Suggest you stick to the specified format of hh:mm:ss then you can add time (fairly) easily.

A Helper column with this formula

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

will convert it in one sweep based on your (extremely limited) data sample.

You'll probably have to amend this formula if you've entered any hours.

**Ace_XL** · 07-08-2013, 12:49 PM

=A1/60

should get you to the desired mm:ss

**ctrapper** · 07-08-2013, 01:00 PM

Perfect! They both worked, thanks!

**knight17** · 07-14-2013, 04:39 PM

Excel newbie here, amazed at this and would like to understand why these methods work.

Please Login or Register  to view this content.

Here is my idea of why this is working. Since the formula specifically zero out the hour argument, the minute(A1) will look at A1 and seeing that it is a time-cell, take the first value of it as minute, same goes for seconds (it take the next value in A1 time-cell, the minute part) Is this how this is working? Or am I completely off the mark? I've read the TIME() help file and couldn't find anything to understand this more.

Originally Posted by Ace_XL

=A1/60

should get you to the desired mm:ss

We are dividing a time-cell by 60 and we obtain a a time-cell with 00:03:47. How so? I think this is not to do with Excel but general (lack of) mathematical thinking. Will you please explain it briefly?