I have tried a number of different ways to format a field which would show the total days, hours, minutes and seconds from a range (see attached) it seems as though the field will not accept any number above 30/31 days I expect. Any help would be greatly appreciated. thanks in advance.
Last edited by barber87; 05-05-2017 at 11:20 AM.
I don't think excel supports that (just doing it with formatting) you probably need to write a formula.
If you want something done right... find a forum and ask an online expert.
Time flies like an arrow. Fruit flies like a banana.
Thanks, any clues a to what sort of formula would be best to use.
Try this ...
=INT(SUM(B5:B87))&" days, "&TEXT(SUM(B5:B87),"hh"" hours, ""mm"" minutes and ""ss"" seconds""")
Phuocam thank you so much that has worked perfectly, really appreciate it
I have taken the Formula that was suggested above and amended this slightly to fit the full worksheet i am using. I now need a way to make sure that empty cells are not included in the total count as the total I now have seems far to large to not include these.
=INT(SUM(E:E))&" days, "&TEXT(SUM(E:E),"hh"" hours, ""mm"" minutes and ""ss"" seconds""")
I think that I see the problem(s), providing column E in the actual data is set up like column B in the sample attached to post #1.
1) the sum includes today's date, which excel actually records as being 42865 (the number of days since 1/1/1900)
2) each entry in column B displays an offset number of days, that is if the current date is the 5th (as in the sample file) and the status date is the 22nd then the backlog displays 13 (cell B5) although there are actually 43 days between May 5th and March 22nd.
This proposed solution applies the following variation of Phuocam's formula to column B:It employs a helper column (C) populated by the formula: =IF(B5<>"",B$3-A5,0)Formula:
Please Login or Register to view this content.
The formula that displays the total is:Let us know if you have any questions.Formula:Please Login or Register to view this content.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you JeteMC that works great. I just need to transfer this to the full workbook and tweak some cell references but should work great. Thanks again.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks