re calculating numbers to times

[Hein]

Hello
I hope somebody can help me with the following:

Has anybody ever tried to recalculate numbers to times?
Let me be more specific: if I have 2 times, let's say 37:28 seconds and 36:94 seconds, how can I re-calculated this to 1:14:22?

Adding the 28 and 94 gives me 1 sec 22: for the seconds:
(int(122/100)=1, the rest = 122 -/- (int(122/100)*100) =22
37 and 36 = 73 which equals 1 min 13 secs
(similar: mins = int(73/60), secs = 73 -/- (int(73/60)*60) = 13

Together: 1 min, 13 + 1 = 14 seconds and 22

My problem is that I don't know how to translate this into a formula, or better VBA?

I think that if I want to capture this only in formulas I may need a lot of columns for 1 calculation, so if it is possible in VBA this has my preference!

Who can help me?

Thanks for your help
Hein

[Myrna Larson]

I assume you mean 37 and 28/100 seconds, and 36 and 94/100 seconds? If so you
can enter these times in a worksheet cell using this data entry format:
00:00:37.28 and 00:00:36.94. Set the format of the cells to hh:mm:ss.00 Then
simply add the cells (e.g. =A1+B1). YOu may have to set the format of the
result cell, too.

You can also enter the numbers as 37.28 and 36.94. Then to add them, use the
formula

=(A1+B1)/86400

and format as above. Excel stores a time as a fraction of a day. The division
by 86400 converts seconds to days.



On Sun, 2 Oct 2005 14:49:22 -0500, Hein
<[email protected]> wrote:

>
>Hello
>I hope somebody can help me with the following:
>
>Has anybody ever tried to recalculate numbers to times?
>Let me be more specific: if I have 2 times, let's say 37:28 seconds and
>36:94 seconds, how can I re-calculated this to 1:14:22?
>
>Adding the 28 and 94 gives me 1 sec 22: for the seconds:
>(int(122/100)=1, the rest = 122 -/- (int(122/100)*100)
>=22
>37 and 36 = 73 which equals 1 min 13 secs
>(similar: mins = int(73/60), secs = 73 -/-
>(int(73/60)*60) = 13
>
>Together: 1 min, 13 + 1 = 14 seconds and 22
>
>My problem is that I don't know how to translate this into a formula,
>or better VBA?
>
>I think that if I want to capture this only in formulas I may need a
>lot of columns for 1 calculation, so if it is possible in VBA this has
>my preference!
>
>Who can help me?
>
>Thanks for your help
>Hein

[Hein]

Thank you Myrna for your reply,

I think I was a bit hasty posting my question, I should have been clearer.

The situation is as follows:

The mentioned times do not have the ":". In fact the 37:28 and 36:94 are written as 3728 and 3694 ànd are text values.

Maybe I should try to make my own formula in VBA?

Maybe I should explain what it is I want to do:
I am building a database for speedskating times. This database is fed with data I first prepare in Excel.

One type of data are laptimes. Sometimes I only have the times for each lap, sometimes I only have the accumulated times, adding up to the final time. My goal is to calculate the missing times, starting with adding up the laptimes to a final time.

The first step in this is trying to add the 3728 and 3694.

How can I create a formula in VBA that adds up these numbers, taking into account:
28 + 94 = 122. This is 1 second, 22/100
37 + 36 = 73. This is 1 min, 13 secs
Together: 1 mins, 13 + 1 sec, and 22/100: 1:14:22
Is there a way to do this

Hope you can help me
Thanks Hein