Date Time Comparison

[yessir]

I'm trying to use the data in a pivot table and the information is entered into one cell as dd hh:mm:ss. How would I break this out if I wanted to manipulate the data, would it be with a pivot table or formula? I have two columns next to each other with dd hh:mm:ss format with different times and I want to calculate the time elapsed between them. Anything helps!

 

[Cubist]

That means they're text strings, not date/time data type. Try this.

Book1
	A	B	C	D	E
1	Time1	Time2	Days1	Days2	Difference in hours
2	2 4:30:01	2 5:01:01	2.188	2.209	0.52
3	4 23:55:00	5 00:25:00	4.997	5.017	0.50
4	5 22:12:00	6 01:00:00	5.925	6.042	2.80
5	1 12:00:01	1 17:00:00	1.500	1.708	5.00
Sheet2

Cell Formulas
Range	Formula
C2:D5	C2	=LET(space,FIND(" ",A2),t,--LEFT(A2,space-1)+--TRIM(MID(A2,space+1,8)),t)
E2:E5	E2	=(D2-C2)*24

 

[yessir]

That does work. Is Column E able to be formatted into hh:mm:ss?

 

[Cubist]

Yes. Leave column E in days i.e. don't multiply by 24. Ctrl + 1 -> Format cells -> Custom -> [h]:mm:ss

Book1
	A	B	C	D	E
1	Time1	Time2	Days1	Days2	Difference in hours
2	2 4:30:01	2 5:01:01	2.188	2.209	0:31:00
3	4 23:55:00	5 00:25:00	4.997	5.017	0:30:00
4	5 22:12:00	6 01:00:00	5.925	6.042	2:48:00
5	1 12:00:01	1 17:00:00	1.500	1.708	4:59:59
Sheet2

Cell Formulas
Range	Formula
C2:D5	C2	=LET(space,FIND(" ",A2),t,--LEFT(A2,space-1)+--TRIM(MID(A2,space+1,8)),t)
E2:E5	E2	=D2-C2

 

[FormR]

Hi, here's another option you could also try:

Book1
	A	B	C	D
1	Time1	Time2	Difference in hours
2	2 4:30:01	2 5:01:01	0:31:00	<Formated with [h]:mm:ss
3	4 23:55:00	5 00:25:00	0:30:00
4	5 22:12:00	6 01:00:00	2:48:00
5	1 12:00:01	1 17:00:00	4:59:59
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=("1900-1-"&B2)-("1900-1-"&A2)

 

[Phuoc]

Try this:

Book1
	A	B	C
1	Time1	Time2	Difference in hours
2	4:30:01	3 4:30:01	72:00
3	34 23:55:00	35 00:25:00	0:30
4	50 22:12:00	51 01:00:00	2:48
5	31 12:00:01	32 17:00:00	28:59
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(LEFT(B2,FIND(" ",B2)-1),0)+IFERROR(MID(B2,FIND(" ",B2)+1,8),B2)-IFERROR(LEFT(A2,FIND(" ",A2)-1),0)-IFERROR(MID(A2,FIND(" ",A2)+1,8),A2)