Cumulative days roll-over

[John MacAllister]

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

 

[OssieMac]

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.

 

[John MacAllister]

OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John

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

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

 

[OssieMac]

Sorry. I initially misread your question. It is the days that you are having
a problem with not the hours.

--
Regards,

OssieMac

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

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

 

[Fred Smith]

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.

OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John

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

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

 

[OssieMac]

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

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.

OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John

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


:

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

 

[John MacAllister]

Thanks! I'll try it.
John

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

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.

OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John


:

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


:

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

 

[John MacAllister]

Thanks, Fred. I appreciate the help.
John

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.

OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John

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


:

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