Time formatting

[jimmyjimmyv]

I have a spreadsheet that is generated nightly by our ERP system and I think the timestamps are being entered in 2 different way, or something? When I try to pivot the table, I can't group the time nor sort it by time. I've tried every different time format but nothing seems to work. I think some of the cells may have leading spaces maybe? I'm hoping someone can point me in the direction to reformat this data so I can use it in a group, or at least sort it by time. Thank you so much if you're able to help.

Daily Scrap Report 01-23.xlsx
	A	B
1	Time	Qty
2	3:02:35 PM	1
3	8:24:35 AM	633
4	8:24:54 AM	572
5	4:23:09 PM	55
6	9:57:14 AM	53
7	6:51:49 AM	34
8	12:04:28 AM	130
9	12:02:20 AM	130
10	6:57:18 PM	538
11	2:32:31 PM	84
12	2:58:42 AM	135
13	10:22:44 AM	138
14	3:34:56 AM	379
15	1:41:39 AM	84
16	12:15:25 AM	270
17	8:03:35 PM	1,775
18	2:20:50 PM	20
19	12:29:14 PM	119
20	6:38:15 AM	12
21	8:21:13 AM	48
22	2:46:08 PM	47
23	6:33:52 AM	21
24	2:49:08 PM	14
25	2:34:54 PM	5
26	2:27:55 PM	82
27	12:37:04 PM	53
28	10:01:48 AM	270
Sheet2

 

[Fluff]

How about in C2 copied down

=TRIM(A2)+0

and formatted as Time

 

[Dave Patton]

try
=IF(LEN(A2)>11,A2,--RIGHT(A2,LEN(A2)-1))

 

[jimmyjimmyv]

How about in C2 copied down

=TRIM(A2)+0

and formatted as Time

try
=IF(LEN(A2)>11,A2,--RIGHT(A2,LEN(A2)-1))

Thank you both.. both of these formulas work!!

 

[Dave Patton]

Thanks for the feedback.

 

[Fluff]

Glad we could help & thanks for the feedback.