Adding Total Number of Days Elapsed and Displaying >31 Days

**cwwazy** · 07-02-2013, 01:10 PM

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

**XOR LX** · 07-02-2013, 02:39 PM

Hi,

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

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

Regards

**daddylonglegs** · 07-02-2013, 02:46 PM

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

**cwwazy** · 07-02-2013, 03:28 PM

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

**XOR LX** · 07-02-2013, 03:43 PM

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