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.

 

[Hein]

Thank you Myrna for your reply,

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

The situation is as follows:

The mentioned times do not have the ":". In fact the 37:28 and 36:9
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 fe
with data I first prepare in Excel.

One type of data are laptimes. Sometimes I only have the times for eac
lap, sometimes I only have the accumulated times, adding up to the fina
time. My goal is to calculate the missing times, starting with adding u
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, takin
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 Hei