+ Reply to Thread
Results 1 to 6 of 6

Adding Total Number of Days Elapsed and Displaying >31 Days

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    18

    Adding Total Number of Days Elapsed and Displaying >31 Days

    Hello All,

    I'm trying to total the number days elapsed for Projects worked in a month in DD HH:MM:SS format. When the days total < 31 days, the forumula calculates correctly, but if it's greater, the days start over again at 1.
    How do I display the Total Project hours added in a month displayed in DD HH:MM:SS for hours totaling over >31 days.

    Please see attached spreadsheet. I have a sample of Projects completed in December and January.
    The table in December is showing the correct since the days totaled are < 31 days, but the table in January is not showing correctly.

    Elapsed Time Wks.xlsx

    Thanks!

    Regards,
    Andy

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,744

    Re: Adding Total Number of Days Elapsed and Displaying >31 Days

    Hi,

    You'll need to use an extra cell (and hide your current one):

    =INT(D17)&" "&TEXT(MOD(D17,1),"hh:mm:ss")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Adding Total Number of Days Elapsed and Displaying >31 Days

    If it's only the totals that might exceed 31 then you can use a similar approach to XOR LX's suggestion (although you don't need MOD) without requiring an extra cell, so in D17

    =INT(SUM(D12:D15))&TEXT(SUM(D12:D15)," hh:mm:ss")

    ...I assume that B12 should be 1/1/2013
    Audere est facere

  4. #4
    Registered User
    Join Date
    06-20-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Adding Total Number of Days Elapsed and Displaying >31 Days

    Hi XOR LX / Daddylonglegs,

    Thanks for the guidance and the formula.
    Yes, B12 should be 1/1/2013

    Sorry, should have asked it in the original question.
    What happens if the elasped time for an individual project was > than 31 days? How would I display that in DD HH:MM:SS without the formula being in Text format? If it's in text format, I wouldn't be able to sum the Total Project Days for the whole month right? I guess I might have to create another column to display that?

    I have attached the sheet again adding February. I added the formula you showed me in Column E. Just wanted to check if that was the best way to do it?

    Elapsed Time Wks.xlsx

    Thanks again for the help!

    Regards,
    Andy

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,744

    Re: Adding Total Number of Days Elapsed and Displaying >31 Days

    Yes, you are correct that you will not be able to sum any text entries, which is why it may be best to have an extra, hidden column containing the Elapsed Times in General format, from which the calculations can be made, and another with the 'text' formats as described.

    Regards

  6. #6
    Registered User
    Join Date
    06-20-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Adding Total Number of Days Elapsed and Displaying >31 Days

    Thanks again for the guidance!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1