Time calculations

[Ian M]

Hi,

Please help, I need to add and subtract columns of data expressed as
H:mm:ss.sss. That is, for example, 1:30:15.07 being 1 hour 30 minutes 15
seconds and 7 hundreths of a second. Can someone please advise how to do
this in excel. I am running XP Pro.

I want to easily compare and contrast swimmers and runners times for a model
I am building. I need the ability to compare different athletes times and
determine the difference in their performances using the power of excel
instead of my calculator.

Cheers

 

[Jerry W. Lewis]

Time is stored as fractions of a day, so 1:30:15.07 if formatted to
General would be
0.0626744212962963 = 1/24 +30/24/60 +15.07/24/60/60
Consequently, you can simply add and subtract times.

If the answer is negative then time formats will show an error, so you
will have to format it as a number and interpret it directly.

If the answer exceeds 24 hours and you want a time format, the format
should involve [h] instead of h to prevent the days from being ignored.

Jerry

 

[Ron Rosenfeld]

If the answer is negative then time formats will show an error, so you
will have to format it as a number and interpret it directly.

OR use the 1904 date system.


--ron

 

[Ian M]

OK Thanks,

But can I add and subtract data in H:mm:ss.sss without converting it first
to decimals. I am talking thousands of records here. The final solution
will be a purpose built DB, but I want to test the outputs of the model with
lots of live data. It is Ok if excel converts H:mm:ss.sss to decimals for
me up front as I actually want decimal output at the end. However, I dont
want the joy of converting thousands of pieces of H:mm:ss.sss to decimals
manually.

If I cant do the H:mm:ss.sss conversion to decimals with an excel short cut
I will just do limited testing with the live data. It will take a while
instead of minutes.

Cheers

Time is stored as fractions of a day, so 1:30:15.07 if formatted to
General would be
0.0626744212962963 = 1/24 +30/24/60 +15.07/24/60/60
Consequently, you can simply add and subtract times.

If the answer is negative then time formats will show an error, so you
will have to format it as a number and interpret it directly.

If the answer exceeds 24 hours and you want a time format, the format
should involve [h] instead of h to prevent the days from being ignored.

Jerry

Hi,

Please help, I need to add and subtract columns of data expressed as
H:mm:ss.sss. That is, for example, 1:30:15.07 being 1 hour 30 minutes 15
seconds and 7 hundreths of a second. Can someone please advise how to do
this in excel. I am running XP Pro.

I want to easily compare and contrast swimmers and runners times for a model
I am building. I need the ability to compare different athletes times and
determine the difference in their performances using the power of excel
instead of my calculator.

Cheers

 

[Ron Rosenfeld]

But can I add and subtract data in H:mm:ss.sss without converting it first
to decimals.

Yes.

If

A1: 1:13:20.25
B1: 0:59:10.85

then =A1+B1: 2:12:31.10

Format as [h]:mm:ss.00

What Jerry was trying to tell you is that even though you are displaying the
results as times, Excel is really storing them as decimal numbers. You don't
have to do any conversions in most cases, if you are just working with times.

There are some limitations.

If you are using the 1900 date system, XL will not *display* negative times.
It will if you use the 1904 date system, but this may cause an issue with
regard to date displays being different by four years if you are switching back
and forth between the systems.

Also, you cannot *directly enter* times that are equal to or greater than
10,000 hours. And there is some other limit on using times that are more than
about 10^7 hours.




--ron

 

[Jerry W. Lewis]

My point was not for you to do the conversion, but to show that you can
do the math directly. "h:mm:ss.000" is simply a custom format, not a
new data type. When you ask Excel to do math it works with the stored
value, independent of the format.

As I noted in my previous post, the only time you might not be happy
with the result of just doing the math with the times is if the result
is negative (then you have to format as a number and convert the answer
only) or if the sum of the times exceeds 24 (use "[h]:mm:ss.000" to
avoid ignoring the multiples of 24 hours).

Jerry

OK Thanks,

But can I add and subtract data in H:mm:ss.sss without converting it first
to decimals. I am talking thousands of records here. The final solution
will be a purpose built DB, but I want to test the outputs of the model with
lots of live data. It is Ok if excel converts H:mm:ss.sss to decimals for
me up front as I actually want decimal output at the end. However, I dont
want the joy of converting thousands of pieces of H:mm:ss.sss to decimals
manually.

If I cant do the H:mm:ss.sss conversion to decimals with an excel short cut
I will just do limited testing with the live data. It will take a while
instead of minutes.

Cheers

Time is stored as fractions of a day, so 1:30:15.07 if formatted to
General would be
0.0626744212962963 = 1/24 +30/24/60 +15.07/24/60/60
Consequently, you can simply add and subtract times.

If the answer is negative then time formats will show an error, so you
will have to format it as a number and interpret it directly.

If the answer exceeds 24 hours and you want a time format, the format
should involve [h] instead of h to prevent the days from being ignored.

Jerry

Ian M wrote:

Hi,

Please help, I need to add and subtract columns of data expressed as
H:mm:ss.sss. That is, for example, 1:30:15.07 being 1 hour 30 minutes 15
seconds and 7 hundreths of a second. Can someone please advise how to

do

this in excel. I am running XP Pro.

I want to easily compare and contrast swimmers and runners times for a

model

I am building. I need the ability to compare different athletes times

and

determine the difference in their performances using the power of excel
instead of my calculator.

Cheers

 

[Jerry W. Lewis]

Interesting! I don't think I will change, since that would reinterpret
all existing dates to shift by 4 years, but I do wonder why MS supports
negative time in one system and not the other.

Jerry