Converting time

**braydon16** · 02-27-2009, 02:17 PM

I have a worksheet that I need to enter minutes and seconds into. I also have some information already in it. I am trying to find a faster way then typing "00:00:35" into each cell. I need to add the time and analyze it later, so I'm not sure where to go from here. Help!

**DonkeyOte** · 02-27-2009, 02:33 PM

Entering time in time format is always advisable (obviously) ... you could enter as an integer and handle the issue later when doing subsequent calcs, eg:

A1: 335 = 3 mins 35 seconds
A2: 1000 = 10 mins 0 seconds
A3: 745 = 7 mins 45 seconds

obviously it's important for seconds you use 2 digits where min > 0... eg 1 min 5 should be entered as 105 and not 15.

You could sum the above for example using:

=--TEXT(SUM(A1:A3),"00\:00")

Formatted to [hh]:mm this would show as 21:20

Daddylonglegs may have an alternative approach if he sees it...

You could use a VBA change event to handle the data as soon as you enter it but this would be slightly slower to execute... the above approach may be preferable ?

EDIT: in hindsight the above example for summation is flawed ...

**blane245** · 02-27-2009, 02:35 PM

You could use a set of three columns - one containing minutes, one containing seconds, and then the other having the formula

=TIMEVALUE(CONCATENATE("0:",A1,":",B1))

where A1 contains the minutes and B1 contains the seconds.

**DonkeyOte** · 02-27-2009, 02:40 PM

blane, you can actually make that simpler if you adopt it...

=TIME(0,A1,B1)

**blane245** · 02-27-2009, 02:41 PM

Excellent!

**braydon16** · 02-27-2009, 02:54 PM

When I enter either of the =TIMEVALUE(CONCATENATE("0:",A1,":",B1)) or =TIME(0,A1,B1), all I get is 12:00 AM. I am looking for the actual minutes.

If A1 is 2 and B1 is 15, I am looking for C1 (assuming this is where the formula is) to return 2:15.

**DonkeyOte** · 02-27-2009, 02:55 PM

re: earlier Sum... the below would work (I think) to Sum integer values
(ie eg A1 = 215 etc...)

=SUMPRODUCT(TEXT(A1:A5,"00\:00")/60)

Above cell Formatted to [mm]:ss

EDIT

re: your last post... are you sure -- I could understand TIME(0,A1,B1) showing 12:02 AM but not 12:00 AM ... set a Custom Format of [m]:ss on the cell containing the formula.