| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cumulative days roll-over
Hello. I have a simple spreadsheet that tracks my running. I enter the
miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Format the cell as [h]:mm.
The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how would you advise? Thanks. John "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Sorry. I initially misread your question. It is the days that you are having
a problem with not the hours. -- Regards, OssieMac "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Unfortunately, Excel does not support a format of [dd] the way it does [hh].
You will need to split the cell. =int(celladdress) will give you just the days -- format it as general. Format the other cell as hh:mm Regards, Fred. "John MacAllister" wrote in message ... OssieMac, Very helpful, thanks. Now, if I wanted to display the number of days, how would you advise? Thanks. John "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Fred's post gave me another idea to display as dd hh:mm:ss
Assuming the the value is in cell B2 then the following formula will display the result in the desired format. However, you will not be able to use it in calculations; only as your displayed result because it is actually text. =INT(B2) & " " & TEXT(B2,"hh:mm:ss") You can perform all your calculations as you have been doing them and perhaps hide the column or row and use the column or row with the above formula for the display of the results in the desired format. -- Regards, OssieMac "Fred Smith" wrote: Unfortunately, Excel does not support a format of [dd] the way it does [hh]. You will need to split the cell. =int(celladdress) will give you just the days -- format it as general. Format the other cell as hh:mm Regards, Fred. "John MacAllister" wrote in message ... OssieMac, Very helpful, thanks. Now, if I wanted to display the number of days, how would you advise? Thanks. John "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Thanks! I'll try it.
John "OssieMac" wrote: Fred's post gave me another idea to display as dd hh:mm:ss Assuming the the value is in cell B2 then the following formula will display the result in the desired format. However, you will not be able to use it in calculations; only as your displayed result because it is actually text. =INT(B2) & " " & TEXT(B2,"hh:mm:ss") You can perform all your calculations as you have been doing them and perhaps hide the column or row and use the column or row with the above formula for the display of the results in the desired format. -- Regards, OssieMac "Fred Smith" wrote: Unfortunately, Excel does not support a format of [dd] the way it does [hh]. You will need to split the cell. =int(celladdress) will give you just the days -- format it as general. Format the other cell as hh:mm Regards, Fred. "John MacAllister" wrote in message ... OssieMac, Very helpful, thanks. Now, if I wanted to display the number of days, how would you advise? Thanks. John "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cumulative days roll-over
Thanks, Fred. I appreciate the help.
John "Fred Smith" wrote: Unfortunately, Excel does not support a format of [dd] the way it does [hh]. You will need to split the cell. =int(celladdress) will give you just the days -- format it as general. Format the other cell as hh:mm Regards, Fred. "John MacAllister" wrote in message ... OssieMac, Very helpful, thanks. Now, if I wanted to display the number of days, how would you advise? Thanks. John "OssieMac" wrote: Format the cell as [h]:mm. The square brackets around the h tell it to display the hours instead of thinking it is the next day. Go into number format-time-custom and enter the format as above. -- Regards, OssieMac "John MacAllister" wrote: Hello. I have a simple spreadsheet that tracks my running. I enter the miles run and elapsed time in two columns. The format in the time column is hh:mm:ss. Two more columns track cumulative distance and cumulative time. The format used in the cumulative time cell is d hh:mm. Once the total cumulative time reaches 31 23:59, the next cell returns: 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Roll Under- sideways | New Users to Excel | |||
| Roll Up | New Users to Excel | |||
| auto number roll over? | Excel Worksheet Functions | |||
| rent roll | Excel Discussion (Misc queries) | |||
| trying to create a rent roll | Excel Discussion (Misc queries) | |||
Linear Mode
