Time between date/time

[JWSafe]

Hi There,
I can't work this out!
this is the data set I am working with and it's basic testing times for each test.
I'm try to work out the time difference between each test.
I think I'm struggling with formatting.

The main issue I'm having is when I'm Text to Columns it's converting the 'pm' times from 1pm to am.

Help is appreciated.

 

[awoohaw]

what does the data in column B look like before you use Text To Columns?
Have you considered using a formula to convert the text to the time value?

 

[JWSafe]

Hey, this is what it looks like before I do text to columns.

 

[awoohaw]

do you know if this is 12:32 AM or 12:32 PM. Is all of your data the same time? How are times after 1:00 PM displayed?
Nevermind I didnt see the bottom records

Parse out the time component with this.

=TIMEVALUE(TEXTAFTER(A2," "))

then custom format the cell as hh:mm or hh:mm:ss

 

[JWSafe]

Yer that's the issue after 1pm it converts it to am time

 

[awoohaw]

Yer that's the issue after 1pm it converts it to am time

this is what I get:
(I created the date time text in column A for ease).

Book1
	A	B
1		format hh:mm or hh:mm:ss
2	2024-02-14 00:09:43	00:09
3	2024-02-14 02:36:22	02:36
4	2024-02-14 03:08:46	03:08
5	2024-02-14 03:40:42	03:40
6	2024-02-14 04:32:01	04:32
7	2024-02-14 08:20:49	08:20
8	2024-02-14 08:54:48	08:54
9	2024-02-14 09:25:18	09:25
10	2024-02-14 11:53:48	11:53
11	2024-02-14 12:18:01	12:18
12	2024-02-14 13:21:32	13:21
13	2024-02-14 15:31:43	15:31
14	2024-02-14 15:52:41	15:52
15	2024-02-14 17:44:51	17:44
16	2024-02-14 18:08:26	18:08
17	2024-02-14 21:13:59	21:13
18	2024-02-14 23:34:53	23:34
19	2024-02-14 23:41:43	23:41
20	2024-02-14 23:42:21	23:42
21	2024-02-14 23:57:33	23:57
Sheet3

Cell Formulas
Range	Formula
B2:B21	B2	=TIMEVALUE(TEXTAFTER(A2," "))

 

[JWSafe]

Row 36 as example before and after

 

[JWSafe]

nah didn't work?

 

[awoohaw]

then you have messy data that needs to be cleaned up
I cant see what you data looks like because of the yellow triangle. If you copied this from a web page or imported it somehow, you have non printing or zero length characters.
try this and see if it still displays the date and time text correctly:

=LEFT(A2,LEN(A2)-1)

But, did it work for most of the records? It seems like it did. But, if you're doing time arithmetic, I suggest you use 24 hour display and not AM/PM. it always confuses me.

 

[MARK858]

@JWSafe what do you get with

=TIMEVALUE(TEXTAFTER(TEXT(A2,"dd/m/yyyy hh:mm")," "))

or just

=TIMEVALUE(TEXT(A2,"dd/m/yyyy hh:mm"))