 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Cumulative 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 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.  SignatureRegards, 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 [quoted text clipped - 3 lines] > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > John Sorry. I initially misread your question. It is the days that you are having a problem with not the hours. SignatureRegards, OssieMac > Format the cell as [h]:mm. > [quoted text clipped - 10 lines] > > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > > John 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. > [quoted text clipped - 10 lines] > > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > > 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 [quoted text clipped - 19 lines] >> > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. >> > 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. SignatureRegards, OssieMac > Unfortunately, Excel does not support a format of [dd] the way it does [hh]. > You will need to split the cell. [quoted text clipped - 28 lines] > >> > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > >> > John Thanks! I'll try it. John > Fred's post gave me another idea to display as dd hh:mm:ss > [quoted text clipped - 40 lines] > > >> > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > > >> > John 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. [quoted text clipped - 28 lines] > >> > 1 hh:mm instead of 32 hh:mm. Am I doing something wrong? Many thanks. > >> > John |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.