Need help!!

L

lon

I am timing car racing and need to display the total of 2 times (entere
as numbers to 2 decimal places to represent seconds) as minutes an
seconds.

Eg: 0.37 + 0.35 displayed as 1:12sec.

How do I do this??!
 
N

Norman Harker

Hi Ion!

Try:
=INT(A1)/(24*60)+MOD(A1,1)*(100/60)/(24*60)+INT(B1)/(24*60)+MOD(B1,1)*
(100/60)/(24*60)
Format mm:ss

I've added the INTs to cover you for entries of (e.g.)1.37

If this isn't possible use:

=MOD(A1,1)*(100/60)/(24*60)+MOD(B1,1)*(100/60)/(24*60)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

Hi
you can use Normans working solution but the formulas would be easier
if you enter your times separated with a colon. e.g. if you neter
0:37 and 0:35 (format these cells as time), you can simply add them:
A1+B1
 
T

Tom Ogilvy

Just to add:

0:37 and 0:35 would be 37 and 35 minutes. If the OP wanted to enter minutes
and seconds

0:00:37, 0:00:35

would be 37 and 35 seconds (although the OP's numbers were hundredths of
minutes).
 
N

Norman Harker

Hi Tom!

Re: "OP's numbers were hundredths of minutes"

OP used 0.37 as representing 37 seconds not .37 of a minute.

But entry using a form recognised as time by Excel must be preferred
even if it is a few more keystrokes.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
T

Tom Ogilvy

so I didn't need to qualify my answer, it was even more correct. To enter
35 seconds as a time value requires 0:00:35 rather than 0:35 which is 35
minutes.

True I didn't spend a lot of time deciphering this mess in the OP's post:
Eg: 0.37 + 0.35 displayed as 1:12sec.
Click to expand...

I was responding to Frank's time entry notation.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

Norman Harker said:
Hi Tom!

Re: "OP's numbers were hundredths of minutes"

OP used 0.37 as representing 37 seconds not .37 of a minute.

But entry using a form recognised as time by Excel must be preferred
even if it is a few more keystrokes.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Click to expand...
 
Top