Removing extra spaces then countifs issue

M

Manny74

Board Regular
Joined
May 6, 2016
Messages
124
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


[TABLE="width: 402"]
<tbody>[TR]
[TD="width: 29, bgcolor: transparent"]
A
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
B​
[/TD]
[TD="width: 99, bgcolor: transparent"]
C​
[/TD]
[TD="width: 92, bgcolor: transparent"]
D​
[/TD]
[TD="width: 64, bgcolor: transparent"]
E​
[/TD]
[TD="width: 94, bgcolor: transparent"]
F​
[/TD]
[TD="width: 94, bgcolor: transparent"]
G​
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"] ID
[/TD]
[TD="width: 64, bgcolor: transparent"] DEPT
[/TD]
[TD="width: 99, bgcolor: transparent"] CUSTOMER
[/TD]
[TD="width: 92, bgcolor: transparent"] ORDER DATE
[/TD]
[TD="width: 64, bgcolor: transparent"] ORDER TIME
[/TD]
[TD="width: 94, bgcolor: transparent"] DISCHARGE DATE
[/TD]
[TD="width: 94, bgcolor: transparent"] DISCHARGE TIME
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
1​
[/TD]
[TD="width: 64, bgcolor: transparent"] 3E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JOHN
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/12/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 11:50
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/12/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 12:30
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
2​
[/TD]
[TD="width: 64, bgcolor: transparent"] 3E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE,JUNE
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/19/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 09:30
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/19/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 11:30
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
3
​
[/TD]
[TD="width: 64, bgcolor: transparent"] 3E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE,JANE
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/25/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 12:30
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/25/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 20:30
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"] 4E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JOE
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/26/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 14:00
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/26/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 18:00
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
5
​
[/TD]
[TD="width: 64, bgcolor: transparent"] 4E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JAKE
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/31/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 10:00
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/31/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 11:50
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
6​
[/TD]
[TD="width: 64, bgcolor: transparent"] 4E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JILL
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/12/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 06:30
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/12/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 10:45
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
7
​
[/TD]
[TD="width: 64, bgcolor: transparent"] 6E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JIM
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/19/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 17:30
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/19/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 19:30
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
8
​
[/TD]
[TD="width: 64, bgcolor: transparent"] 6E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JUDE
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/25/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 15:30
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/25/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 21:30
[/TD]
[/TR]
[TR]
[TD="width: 29, bgcolor: transparent"]
9
​
[/TD]
[TD="width: 64, bgcolor: transparent"] 9E
[/TD]
[TD="width: 99, bgcolor: transparent"] DOE, JENN
[/TD]
[TD="width: 92, bgcolor: transparent"]
5/26/2018​
[/TD]
[TD="width: 64, bgcolor: transparent"] 06:00
[/TD]
[TD="width: 94, bgcolor: transparent"]
5/26/2018​
[/TD]
[TD="width: 94, bgcolor: transparent"] 11:00
[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 395"]
<tbody>[TR]
[TD="width: 41, bgcolor: transparent"] Unit
[/TD]
[TD="width: 240"] Order by 12PM
[/TD]
[TD="width: 246"] D/C by 12PM
[/TD]
[/TR]
[TR]
[TD="width: 41, bgcolor: transparent"] 3E
[/TD]
[TD="width: 240"]
=COUNTIFS(B:B,"N3SE",H:H,"<12:00")​
[/TD]
[TD="width: 246"]
=COUNTIFS(B:B,"N3SE",J:J,"<12:00")​
[/TD]
[/TR]
[TR]
[TD="width: 41, bgcolor: transparent"] 4E
[/TD]
[TD="width: 240"]
​
[/TD]
[TD="width: 246"]
​
[/TD]
[/TR]
[TR]
[TD="width: 41, bgcolor: transparent"] 6E
[/TD]
[TD="width: 240"]

​
[/TD]
[TD="width: 246"]
​
[/TD]
[/TR]
[TR]
[TD="width: 41, bgcolor: transparent"] 9E
[/TD]
[TD="width: 240"]
​
[/TD]
[TD="width: 246"]
​
[/TD]
[/TR]
</tbody>[/TABLE]
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?

 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Replace =TRIM(E1) with =TRIM(E1)+0
Format as needed and use 12:00:00 as time criteria in your formula
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
559
shepcs
S
M
  • Solved
Loop set of 3, within 5 results
2
Replies
11
Views
437
Phuoc
P
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
A
  • Question Question
VBA help: Removing Patient from list that has Discharged, and for non-discharged getting the time period
Replies
1
Views
212
eiloken
E

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top