Time won't add up

[jackfox68]

I am pulling this data from an application report (Teleopti) and the time is showing as general. When I use text to column to convert it to a number it removes anything to the right of the colon. I need to be able to sum the Available Column. Any help would be appreciated.

	Date	Available
	6/30/2019	7:30
	7/1/2019	536:50
	7/2/2019	554:55
	7/3/2019	501:25
	7/8/2019	566:27
	7/9/2019	569:48
	7/10/2019	517:30
	7/11/2019	502:18
	7/12/2019	499:58
	7/13/2019	7:30
	7/15/2019	593:05
	7/16/2019	555:08
	7/17/2019	540:06
	7/18/2019	510:15
	7/19/2019	543:36
	7/22/2019	516:35
	7/23/2019	555:10
	7/24/2019	478:32
	7/25/2019	479:43
	7/26/2019	506:30
	7/27/2019	7:30
	7/29/2019	540:25
	7/30/2019	497:49

 

[Toadstool]

Try formatting as Custom [h]:mm including the Total

JackFox68.xlsx
	B	C
1	Date	Available
2	6/30/2019	7:30
3	7/1/2019	536:50
4	7/2/2019	554:55
5	7/3/2019	501:25
6	7/8/2019	566:27
7	7/9/2019	569:48
8	7/10/2019	517:30
9	7/11/2019	502:18
10	7/12/2019	499:58
11	7/13/2019	7:30
12	7/15/2019	593:05
13	7/16/2019	555:08
14	7/17/2019	540:06
15	7/18/2019	510:15
16	7/19/2019	543:36
17	7/22/2019	516:35
18	7/23/2019	555:10
19	7/24/2019	478:32
20	7/25/2019	479:43
21	7/26/2019	506:30
22	7/27/2019	7:30
23	7/29/2019	540:25
24	7/30/2019	497:49
25
26	Total	10588:35
Sheet1

Cell Formulas
Range	Formula
C26	C26	=SUM(C2:C24)

 

[MARK858]

What result are you expecting? 10588:35?

 

[Dave Patton]

I have not seen Teleopti.

Try the following

T202009b.xlsm
	B	C	D	E
1		7:30
2		536:50:00
3		544:20	Custom format [h]:mm
4
5c

Cell Formulas
Range	Formula
C3	C3	=SUM(C1:C2)

It may be useful if you provide a small example of your data with the Forum's XL2BB.

 

[Dave Patton]

If the column is Text, try the following

T202009b.xlsm
	A	B	C	D	E
1	30-Jun-19	7:30	7.50		1,099.25
2	1-Jul-19	536:50	536.83
3	2-Jul-19	554:55	554.92
4			1,099.25
5c

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(B1:B3*24)
C1:C3	C1	=B1*24
C4	C4	=SUM(C1:C3)

 

[padapinto]

why not break the string first so that the hr will be separate from minutes then use concatenate to join them as time format and the apply the additional and subtraction formula

 

[jackfox68]

why not break the string first so that the hr will be separate from minutes then use concatenate to join them as time format and the apply the additional and subtraction formula

OK that seemed to work with a couple more steps. After concatenate I copied and pasted as number. Then I did Text to Column again and just left the defaults and clicked finished. Then I custom formatted as [H]:mm.

Thanks for the tips everyone.