Calculating Elapsed Time

Originally Posted by ExcelUser55

I'm having trouble calculating elapsed time. I want the answer to state
the number of days, hour, and minutes. In Column E I can get the number
of hours but how do I parse this value to get Hours, days, minutes? I also need to divide the hours into days (ex. 1506/24)

Thank you,

A B C D E
WO_No. REQ_DATE REQ_TIME WO_EST_START ElapTime
1-20240274 1/9/2008 14:22:00 3/12/08 9:00 AM 1506:38:00
1-20240688 1/14/2008 15:40:00 1/23/08 8:30 AM 208:50:00
1-20240985 1/16/2008 13:57:00 2/7/08 4:30 PM 530:33:00

Formula in E is =D2-(B2+C2).

Thank you.

Presumably you mean Column F, the first available column. The following formula will give you the Days, Hours and Minutes in the format dd : hh : mm. If you need the individual values then just break the three elements of the formula into three separate cells.

=IF(E2<C2,MAX(D2-B2-1,0),D2-B2)&" : "&INT(IF((E2-C2)<0,24+(E2-C2)*24,(E2-C2)*24))&" : "&ROUND(MOD((E2-C2)*1440,60),0)

HTH

I'm assuming you have start date in B2, start time in C2 and both end time and date in D2. This formula in E2 will give you a result like "62 days 18:38"

=INT(D2-B2-C2)&" days "&TEXT(D2-B2-C2,"hh:mm")

Note: this results in a text string rather than a numeric value

Thank you for the formula. It worked great.

ExcelUser55