Subtract Dates & Times From Multiple Cells To Get Duration

**sshot1** · 12-20-2012, 10:10 AM

I need to derive the duration of an event from the start & end date & time entered in 4 seperate cells:

Cell B4 = 05/10/2012
Cell C4 = 23:34
Cell D4 = 05/11/2012
Cell E4 = 4:44

I would like the resulting cell to display the duration, in this case it should be "5:10"(5 hours, 10 minutes). I tried =(CONCATENATE(D4," ",E4))-(CONCATENATE(B4," ",C4)) and formatted the cell as "Custom [hh]:mm", but that resuted in the #VALUE! error.

Thanks in advance!!!

**benishiryo** · 12-20-2012, 10:23 AM

hi sshot, try:
=SUM(D4:E4)-SUM(B4:C4)

then format as [h]:mm or any other kinds of format. dates are integers in Excel while time is a decimal. 0.5 is half of 1, so half a day, 12 pm