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

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

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

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

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

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

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

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