Date and Time Calculation

[TomRO]

I need to determine the elapsed days, hours, and minutes between two date and time stamps. How many days, hours and minutes elapsed between 10/15/2014 5:30 PM and 10/15/2014 5:40 PM, or between 9/15/2014 5:30 PM and 11/04/2014 6:30 AM. See the attached spreadsheet sample.

 

[Khalid NGO]

Hi @TomRO,

Firstly welcome to the forum...
There is no file, please upload again.

 

[TomRO]

Sorry. I must have neglected to attach the spreadsheet with my original post.

 

[shrivallabha]

Hi TomRO,

Welcome to Chandoo.org forums. I do not have much experience with time and date so I am just attempting one idea. See if this works for you assuming you have data in B2 and A2 and B2 having larger date:
=TEXT(LEFT(TEXT(B2-A2,"[hh]:mm"),FIND(":",TEXT(B2-A2,"[hh]:mm"))-1)/24,"00")&":"&TEXT(MOD(LEFT(TEXT(B2-A2,"[hh]:mm"),FIND(":",TEXT(B2-A2,"[hh]:mm"))-1),24),"00")&RIGHT(TEXT(B2-A2,"[hh]:mm"),3)

 

[Khalid NGO]

OK no problem,
try this in c2:

=INT(B2-A2)&" days "&TEXT(B2-A2,"h"" hrs ""m"" mins ""s""secs""")

 

[TomRO]

Thank You. Thank You. Shrivallabba's gives me "71,09:30" for days, hours and minutes, and Khalid NGO's gives me 71 Days, 9 Hours, 30 Minutes, 18 Seconds". Perfect!

 

[Khalid NGO]

You welcome...
Thanks for the feedback TomRo,