Calculate the total days hours minutes in a column

[jwsutton]

Hi Gurys,

I'm hoping someone can assist me. I have used the formula:

=INT(C3-B3)&" days "&TEXT(C3-B3,"h"" hrs ""m"" mins """)

to return the results as days hours minutes in D3. I now want to tally column D3:D410 to show the total days hours minutes. I am an excel novice, any assistance would be greatly appreciated.

Regards, Jeremy

 

[steve the fish]

Swap C3-B3 in your formula with SUM(D3:D410)

 

[jwsutton]

Thanks,

I made the change in but the total return a value of '0days0hrs0mins'. Not sure why??

 

[Dave Patton]

Excel 2010
	B	C	D	E
2
3	14-Aug-18 12:00	15-Aug-18 12:00	1
4	15-Aug-18 12:00	16-Aug-18 12:00	1
5	10-Aug-18 12:00	16-Aug-18 14:00	6.08
6			8.08	8 day 02 hours 00 min
7
8	E2 is custom format d "day" hh "hours" mm "min"
9d

Cell Formulas
Range	Formula
D3	=C3-B3
D4	=C4-B4
D5	=C5-B5
D6	=SUM(D3:D5)
E6	=D6

 

[jwsutton]

Thank you for that solution. I was trying to be too tricky. Cheers

 

[Dave Patton]

Excel 2010
	B	C	D	E
1			Custom Formatted	Text
2	Days total	16.1875	16 day(s) 4 hour(s) 30 min	16 days4 hrs 30 mins
3	Rate per day	$1,200.00	$19,425.00	#VALUE!
4
5	14-Aug-18 12:00	15-Aug-18 12:00
6	15-Aug-18 12:00	16-Aug-18 12:00
7	10-Aug-18 12:00	16-Aug-18 14:00
8	08-Aug-18 12:00	16-Aug-18 14:30
9d

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(C5:C8-B5:B8)
D2	=C2
D3	=C3*C2
E2	=INT(C2)&" days "&TEXT(MOD(C2,1),"h"" hrs ""m"" mins """)
E3	=E2*C3

In Excel, date and time are numbers. The numbers can be formatted; with cell B8
General 43320.5
Custom Format 08-08-2018 12:00:00 PM

If you convert the number to formatted Text, you will have useful information but you cannot do regular arithmetic with the result see cell E3.
If you just require the total time, see the Sumproduct formula in cell C2.