Removing extra spaces then countifs issue

[Manny74]

I am trying to get total customers with an order time before(less than) 12:00 PM.

So I extracted data from our system and was able to do asave as, and saved my data as an EXCEL report (.XLS)

Order time and discharge time had 4 leading spaces in frontof each time.
So I created a new column, then entered for example, =TRIM(E1),which removed the 4 leading spaces.
I did this for the order time column and discharge timecolumn.

A ​	B​	C​	D​	E​	F​	G​
ID	DEPT	CUSTOMER	ORDER DATE	ORDER TIME	DISCHARGE DATE	DISCHARGE TIME
1​	3E	DOE, JOHN	5/12/2018​	11:50	5/12/2018​	12:30
2​	3E	DOE,JUNE	5/19/2018​	09:30	5/19/2018​	11:30
3 ​	3E	DOE,JANE	5/25/2018​	12:30	5/25/2018​	20:30
4​	4E	DOE, JOE	5/26/2018​	14:00	5/26/2018​	18:00
5 ​	4E	DOE, JAKE	5/31/2018​	10:00	5/31/2018​	11:50
6​	4E	DOE, JILL	5/12/2018​	06:30	5/12/2018​	10:45
7 ​	6E	DOE, JIM	5/19/2018​	17:30	5/19/2018​	19:30
8 ​	6E	DOE, JUDE	5/25/2018​	15:30	5/25/2018​	21:30
9 ​	9E	DOE, JENN	5/26/2018​	06:00	5/26/2018​	11:00

<tbody> </tbody>

So I created a macro to give me totals, see below:

Unit	Order by 12PM	D/C by 12PM
3E	=COUNTIFS(B:B,"N3SE",H:H,"<12:00")​	=COUNTIFS(B:B,"N3SE",J:J,"<12:00")​
4E	​	​
6E	​	​
9E	​	​

<tbody> </tbody>

My totals are not populating, so Ichanged the format for each time (for Order time and Discharge time): FormatCells, Custom, then I select h:mm
The data still didn’t populate….Iclicked on one of the time cells, and it displayed 11:50, when I doubledclicked on it, it then displayed 11:50:00 AM. When I did this for each timecell, then my data populated in the macro.
I don’t know why I have to double clickon each cell with a time for the new format (h:mm) to work? Any idea why thisis happening? Is there something I can do to prevent this from happening?

 

[arthurbr]

Replace =TRIM(E1) with =TRIM(E1)+0
Format as needed and use 12:00:00 as time criteria in your formula

 

[Peter_SSs]

Another option, I'll describe the manual process but it could be done by macro, would be to select the 'Order Time' column and do a Text-To-Columns -> Delimited -> Next -> Select 'Space' & 'Treat consecutive delimiters as one' -> Next -> In the preview window at the bottom select the first (blank) column and choose above that 'Do not import column' -> Finish
Repeat for the other time column.