Elapsed Time

[KevinT]

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[Bob Phillips]

=MAX(B1-A1,0)

where B1 is the actual time, A1 is the handicap.

 

[Stefi]

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

 

[KevinT]

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[Roger Govier]

Hi Kevin

you could use Tools>Options>Calculation>Use 1904 date system
for this workbook.
That will allow negative times.

Be aware that any date calculations will be out by 1432 days on this system,
but I am assuming that dates are not of any interest in this case.

--
Regards
Roger Govier

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[Stefi]

The only way I know is to set Excel Date format to 1904 style:
Tools>Options>Calculation tag>Check 1904 style dates checkbox

Regards,
Stefi


„KevinT†ezt írta:

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[KevinT]

Thanks heaps guys,

The 1904 method worked well. I've no idea why, but hey, it works.

The only thing that concerns me is that I will have to change that option in
every computer I use when I open the file.
More important though is that'll I'll have to change it back when I finish
working on it.

Kevin

Hi Kevin

you could use Tools>Options>Calculation>Use 1904 date system
for this workbook.
That will allow negative times.

Be aware that any date calculations will be out by 1432 days on this system,
but I am assuming that dates are not of any interest in this case.

--
Regards
Roger Govier

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[Stefi]

1904 date system is a workbook specific feature therefore if you transfer the
file to an other computer, the file takes over the 1904 date system as well
without any additional measure.
1904 date system will not apply to other files in your computer unless
explicittely set to them one by one.

Regards,
Stefi


„KevinT†ezt írta:

Thanks heaps guys,

The 1904 method worked well. I've no idea why, but hey, it works.

The only thing that concerns me is that I will have to change that option in
every computer I use when I open the file.
More important though is that'll I'll have to change it back when I finish
working on it.

Kevin

Hi Kevin

you could use Tools>Options>Calculation>Use 1904 date system
for this workbook.
That will allow negative times.

Be aware that any date calculations will be out by 1432 days on this system,
but I am assuming that dates are not of any interest in this case.

--
Regards
Roger Govier

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

:

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[KevinT]

Even better,

Thanks.

1904 date system is a workbook specific feature therefore if you transfer the
file to an other computer, the file takes over the 1904 date system as well
without any additional measure.
1904 date system will not apply to other files in your computer unless
explicittely set to them one by one.

Regards,
Stefi


„KevinT†ezt írta:

Thanks heaps guys,

The 1904 method worked well. I've no idea why, but hey, it works.

The only thing that concerns me is that I will have to change that option in
every computer I use when I open the file.
More important though is that'll I'll have to change it back when I finish
working on it.

Kevin

Hi Kevin

you could use Tools>Options>Calculation>Use 1904 date system
for this workbook.
That will allow negative times.

Be aware that any date calculations will be out by 1432 days on this system,
but I am assuming that dates are not of any interest in this case.

--
Regards
Roger Govier

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

:

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„KevinT†ezt írta:

Even better,

Thanks.

1904 date system is a workbook specific feature therefore if you transfer the
file to an other computer, the file takes over the 1904 date system as well
without any additional measure.
1904 date system will not apply to other files in your computer unless
explicittely set to them one by one.

Regards,
Stefi


„KevinT†ezt írta:

Thanks heaps guys,

The 1904 method worked well. I've no idea why, but hey, it works.

The only thing that concerns me is that I will have to change that option in
every computer I use when I open the file.
More important though is that'll I'll have to change it back when I finish
working on it.

Kevin

:

Hi Kevin

you could use Tools>Options>Calculation>Use 1904 date system
for this workbook.
That will allow negative times.

Be aware that any date calculations will be out by 1432 days on this system,
but I am assuming that dates are not of any interest in this case.

--
Regards
Roger Govier

Thanks Stefi,

It works fine, but is there any way I can display the hours as well?

Kevin

:

Try to format the result cell like [m]

Regards,
Stefi

„KevinT†ezt írta:

Hi,

I have a worksheet in Excel 2000 that I am using for a road race.
I have the names etc of about 100 runners.
Each runner has a handicap time.
I wish to subtract the handicap time from the actual time and rank the
result in order, from lowest to highest.
I'm using the [h]:mm format for the cells.
The problem is that if a runner beats their handicap time the result
displays as #####.
Is there anyway around this?

TIA,
Kevin