Minus Time Additions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee Grant - 12 Dec 2007 15:47 GMT

Hi folks,

I've been having a problem with an Excel sheet that calculates time. Very
simply:

Column A: I have a sprinters lap time (for example 00:02:43 - formatted to
[hh]:mm:ss - so the runner did the lap in 2 minutes 43 seconds)
Column B: I put the sprinters estimated lap time (in the same format)
Column C: I want to calculate the difference between the actual and the
estimated.

Obviously I have encountered a problem when the runner laps slower than the
estimated time:

I found one way around the problem on the internet using this formula:

=IF(B1>A1,B1-A1,TEXT(ABS(B1-A1),"-mm:ss"))

But as this converts the number to TEXT it doesn't allow me to perform the
final task which is put in multiple laps and calculate the total difference
(with the possibility of positive and negative times).

Apart from whipping the runners so they run quicker - is there a way to get
Excel to sort this one out!

Kindest Regards,

Lee

Reply to this Message

Niek Otten - 12 Dec 2007 16:03 GMT

Hi Lee,

Indeed Excel does not show negative times correctly. But you can still calculate with it and it uses the correct value.
One way to show the time is to use the 1904 date system. But that affects all your existing dates and times.
Tools>Options>Calculation tab, check 1904 date system.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi folks,
|
[quoted text clipped - 24 lines]
|
| Lee

Reply to this Message

Peo Sjoblom - 12 Dec 2007 16:09 GMT

If you change the date system to 1904 under tools>options>calculation you
will get negative time

Note that it will change any dates you previously had in that spreadsheet
and add 1462 days to them
so if you use this you must subtract 1462 days from any date after the fact
which is easy enough if you type 1462 in an empty cell, format it the same
way as your dates, copy it, select all dates that were changed and do
edit>paste special and select subtract

Signature

Regards,

Peo Sjoblom

> Hi folks,
>
[quoted text clipped - 24 lines]
>
> Lee

Reply to this Message

Lee Grant - 12 Dec 2007 17:20 GMT

Excellent - so simple yet so hidden!!!

I haven't started on the 'real' project yet - so all the data is new but
thanks for the info about 1462.

You're both excellent.

Thank you, thank you, thank you.

Cheers

Lee

> If you change the date system to 1904 under tools>options>calculation you
> will get negative time
[quoted text clipped - 34 lines]
>>
>> Lee

Reply to this Message

Paul Hyett - 12 Dec 2007 18:21 GMT

In microsoft.public.excel on Wed, 12 Dec 2007, Lee Grant
<lee@inspirationcomputers.com> wrote :

>Hi folks,
>
[quoted text clipped - 3 lines]
>Column A: I have a sprinters lap time (for example 00:02:43 - formatted
>to [hh]:mm:ss - so the runner did the lap in 2 minutes 43 seconds)

2m 43s for a lap - is this the zimmer-frame 'sprint'? :)

Signature

Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Reply to this Message

Lee Grant - 14 Dec 2007 10:01 GMT

It's a big lap ;-)

> In microsoft.public.excel on Wed, 12 Dec 2007, Lee Grant
> <lee@inspirationcomputers.com> wrote :
[quoted text clipped - 7 lines]
>
> 2m 43s for a lap - is this the zimmer-frame 'sprint'? :)

Reply to this Message