| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Elapsed Time
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
=MAX(B1-A1,0)
where B1 is the actual time, A1 is the handicap. -- __________________________________ HTH Bob "KevinT" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
Thanks Stefi,
It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
Hi Kevin
you could use ToolsOptionsCalculationUse 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 "KevinT" wrote in message ... Thanks Stefi, It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
The only way I know is to set Excel Date format to 1904 style:
ToolsOptionsCalculation tagCheck 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 "Stefi" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
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 "Roger Govier" wrote: Hi Kevin you could use ToolsOptionsCalculationUse 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 "KevinT" wrote in message ... Thanks Stefi, It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
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 "Roger Govier" wrote: Hi Kevin you could use ToolsOptionsCalculationUse 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 "KevinT" wrote in message ... Thanks Stefi, It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
Even better,
Thanks. "Stefi" wrote: 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 "Roger Govier" wrote: Hi Kevin you could use ToolsOptionsCalculationUse 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 "KevinT" wrote in message ... Thanks Stefi, It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Elapsed Time
You are welcome! Thanks for the feedback!
Stefi €˛KevinT€¯ ezt Ć*rta: Even better, Thanks. "Stefi" wrote: 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 "Roger Govier" wrote: Hi Kevin you could use ToolsOptionsCalculationUse 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 "KevinT" wrote in message ... Thanks Stefi, It works fine, but is there any way I can display the hours as well? Kevin "Stefi" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
| Elapsed time | Excel Discussion (Misc queries) | |||
| Need Elapsed Time Help | Excel Worksheet Functions | |||
| elapsed time | Excel Worksheet Functions | |||
| Elapsed time | Excel Discussion (Misc queries) | |||
Linear Mode
