Hi! Please help. I wanted to get the summary of the working hours of our fleets operations which is entered in the format 5d 05h 28m. How do i get the summary of this range? Im using microsoft excel 10.

message edited by gretyl

✔ Best Answer

With your data like this: A B C 1) Days Hours & Min 2) 5d 14h 36m 5 14:36 3) 03d 09h 51m 3 9:51 4) 06d 08h 30m 6 8:30 5) 6) 15 Days 8:57:00In cell C2 enter the formula:

=TIME(MID(A2,FIND("d",A2)+2,2),MID(A2,FIND("h",A2)+2,2),)

Drag down as many rows as needed.

In cell B6 enter the formula:

=SUM(B2:B4)+INT(SUM(C2:C4))&" Days"

In cell C6 enter the formula:

=IF(INT(SUM(C2:C4))>=1,SUM(C2:C4)-"24:00:00",SUM(C2:C4))

Cell C6 must be formatted as: [H]:MM:SS

The bracket around the H is required.See how that works for you.

MIKE

Hi, i thinkthis is whatyour looking for

How to sum and format a range of hours, minutes, or seconds in Excel -https://support.microsoft.com/en-us...hope you find a

solution to your problem,please replyif so.

Happy to help

Lakshan Costa

message edited by Lakshancosta

Thanks for your reply Lakshan. However, What im trying to do is adding to getting the sum a range of data in days hours and minutes, i.e 5d 14h 36m + 03d 09h 51m + 06d 08h 30m which sould equal 15d 08h 57m. Right now what I do manually converting the dats into hours so get the summary then convert it again to (Days, Hours, Mins) format whish is a really long process and could get confusing if i have to calculate longer range. I am looking if there is better way to do it or a formula or command in excel to simplify the process.

re: "Im using microsoft excel 10"There is no such thing as

"microsoft excel 10".There is Microsoft Windows 10 (an OS) and there is Microsoft Excel 2010 (an application).

Just for clarification, what do you have?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I meant I have windows 8, ms excel 2010.

message edited by gretyl

With your data like this: A B C 1) Days Hours & Min 2) 5d 14h 36m 5 14:36 3) 03d 09h 51m 3 9:51 4) 06d 08h 30m 6 8:30 5) 6) 15 Days 8:57:00In cell C2 enter the formula:

=TIME(MID(A2,FIND("d",A2)+2,2),MID(A2,FIND("h",A2)+2,2),)

Drag down as many rows as needed.

In cell B6 enter the formula:

=SUM(B2:B4)+INT(SUM(C2:C4))&" Days"

In cell C6 enter the formula:

=IF(INT(SUM(C2:C4))>=1,SUM(C2:C4)-"24:00:00",SUM(C2:C4))

Cell C6 must be formatted as: [H]:MM:SS

The bracket around the H is required.See how that works for you.

MIKE

You may find this solution easier, and more robust. With your data like this:

A B C D 1) Days Hours Minutes 2) 5d 14h 36m 5 14 36 3) 03d 09h 51m 3 9 51 4) 06d 08h 30m 6 8 30 5) 6) 15 Days 8 Hours 57 Min.In cell B2 enter the formula:

=LEFT(A2,FIND("d",A2,1)-1)*1

In cell C2 enter the formula:

=MID(A2,FIND("d",A2)+2,2)*1

In cell D2 enter the formula:

=MID(A2,FIND("h",A2)+2,2)*1

Drag down all three formulas.

In cell B6 enter the formula:

=SUM(B2:B4)+INT(SUM(C2:C4)/24)&" Days"

In cell C6 enter the formula:

=MOD(SUM(C2:C4)/24,1)*24+INT(SUM(D2:D4)/60)&" Hours"

In cell D6 enter the formula:

=MOD(SUM(D2:D4)/60,1)*60&" Min."

And finally, if you want the answer in a single cell

enter this formula, which simply combines all three:=SUM(B2:B4)+INT(SUM(C2:C4)/24)&" Days"&" "&MOD(SUM(C2:C4)/24,1)*24+INT(SUM(D2:D4)/60)&" Hours"&" "&MOD(SUM(D2:D4)/60,1)*60&" Min."

MIKE

Thank you, Mike. Both of your suggestions works for me but i will use this one as i find it easier. Thanks again.

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History