Solved How to add range of days, hours and minutes in exel.

February 1, 2017 at 01:02:51
Specs: Windows 8
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


See More: How to add range of days, hours and minutes in exel.

Report •

✔ Best Answer
February 1, 2017 at 16:14:50
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:00

In 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

http://www.skeptic.com/



#1
February 1, 2017 at 03:24:53
Hi, i think this is what your 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 reply if so.
Happy to help
Lakshan Costa

message edited by Lakshancosta


Report •

#2
February 1, 2017 at 03:45:30
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.

Report •

#3
February 1, 2017 at 05:47:03
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.


Report •

Related Solutions

#4
February 1, 2017 at 08:55:22
I meant I have windows 8, ms excel 2010.

message edited by gretyl


Report •

#5
February 1, 2017 at 16:14:50
✔ 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:00

In 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

http://www.skeptic.com/


Report •

#6
February 1, 2017 at 18:06:13
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

http://www.skeptic.com/


Report •

#7
February 4, 2017 at 22:08:28
Thank you, Mike. Both of your suggestions works for me but i will use this one as i find it easier. Thanks again.

Report •

Ask Question