Minus Time Additions

[Lee Grant]

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

 

[Niek Otten]

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[Lee Grant]

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

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


--


Regards,


Peo Sjoblom

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

 

[Paul Hyett]

In microsoft.public.excel on Wed, 12 Dec 2007, Lee Grant

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)

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

 

[Lee Grant]

It's a big lap ;-)

In microsoft.public.excel on Wed, 12 Dec 2007, Lee Grant

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)

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