Cumulative days roll-over

J

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
 
O

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.
 
J

John MacAllister

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


OssieMac said:
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 said:
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
 
O

OssieMac

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

--
Regards,

OssieMac


OssieMac said:
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 said:
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
 
F

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.

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


OssieMac said:
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 said:
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
 
O

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


Fred Smith said:
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 said:
OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John


OssieMac said:
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
 
J

John MacAllister

Thanks! I'll try it.
John


OssieMac said:
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 said:
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 said:
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
 
J

John MacAllister

Thanks, Fred. I appreciate the help.
John


Fred Smith said:
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 said:
OssieMac,
Very helpful, thanks. Now, if I wanted to display the number of days, how
would you advise?
Thanks.
John


OssieMac said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top