Excel Sum Time Difference in Days and Hours

This video follows the video detailed below and is in response to a question on how to sum the time difference.

Sum of time in Excel over 24 hours can be difficult. This video shows the Excel formula to sum time in days and hours.

You can see the sum of time not working in the video. But then functions such as TRUNC, TEXT and ROUND we can get the answer we were looking for, and in the format, we were hoping for.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[darrylmorgan]

Wicked Tutorial Alan! You Taught me a new Function with TRUNC Thank You :):)

[sasavienne]

Thumbs up.. Thanks Alan. 👍 🌟
Your ideas in Excel are brilliant and always helpful and useful.
Salim

[wayneedmondson]

Hi Alan.. very clever.. love it! I discovered that in this case, the INT() function will also work like the TRUNC() function.. same result. I set up mine to show days, hours and minutes. Here is another solution using TEXT functions such as LEFT and MID.. when pointing to the sum result in cell C14: =LEFT(C14, SEARCH(".", C14)-1) & " days, " & LEFT(TEXT(C14-TRUNC(C14), "h:m"), SEARCH(":", TEXT(C14-TRUNC(C14), "h:m"))-1) & " hours, " & MID(TEXT(C14-TRUNC(C14), "h:m"), SEARCH(":", TEXT(C14-TRUNC(C14), "h:m"))+1, LEN(TEXT(C14-TRUNC(C14), "h:m"))) & " minutes". Or, if wanting to apply it directly to the range of numbers, then replace all the references to C14 with SUM(C2:C12) as in: =LEFT(SUM(C2:C12), SEARCH(".", SUM(C2:C12))-1) & " days, " & LEFT(TEXT(SUM(C2:C12)-TRUNC(SUM(C2:C12)), "h:m"), SEARCH(":", TEXT(SUM(C2:C12)-TRUNC(SUM(C2:C12)), "h:m"))-1) & " hours, " & MID(TEXT(SUM(C2:C12)-TRUNC(SUM(C2:C12)), "h:m"), SEARCH(":", TEXT(SUM(C2:C12)-TRUNC(SUM(C2:C12)), "h:m"))+1, LEN(TEXT(SUM(C2:C12)-TRUNC(SUM(C2:C12)), "h:m"))) & " minutes". This was a great exercise.. really got me thinking. Thanks for the inspiration. Thumbs up!

[DayanTolentino]

Hi Alan hope you can help me howa can I add this two sample (0 Days 4 Hours 0minutes plus 1 Days 1 Hours 57minutes) in excel. Thank you!

[Ashokkumar-kfqq]

Kindly tell me how can i get the hours format if data had in days hours and minute format

[sachinrv]

Hi Alan, I didn't know this TRUNC function either; I would have used MOD instead like
=(C14-MOD(C14, 1))&" "&"Days"&ROUND(MOD(C14, 1)*24, 0)&" "&"Hours", the result of this is
35 Days16 Hours as I have used round

[AmitSharma-ivqf]

Hi Alan, Thanks for the this. I have a question, let's suppose if i want to show that the Difference value is more that 2 Days 48 Hours 00 Minutes than its Outside SLA and if its below than Within SLA. Got stuck here a little bit, can you or anyone help please?


Thanks
Amit

[yousefalghawrien]

Thanks
Just quastion we can do it by change the format to [dd] & " day, " & [hh] & "hours".?
Thanks

[blackvallarta]

Okay, but now I without the dates, just time, like 19:30 to 01:00 equals to 4:30