COUNTIF between period of time, without changing cell format

shondown

New Member
Joined
Aug 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello all, first (of many) post here.
When I pulled the data from my workplace's database, it was formatted something like this -

CreateDt Output
8/29/20 7:19 AM 1
8/29/20 7:19 AM 2
8/29/20 7:25 AM 3
8/29/20 7:25 AM 4
8/29/20 7:25 AM 5
8/29/20 7:25 AM 6
8/29/20 7:25 AM 7
8/29/20 7:25 AM 8
8/29/20 7:30 AM 9
8/29/20 7:30 AM 10
8/29/20 7:30 AM 11
8/29/20 7:30 AM 12
8/29/20 7:30 AM 13
8/29/20 7:30 AM 14

Now, I would like to count the output between 7:00am till 7:30am only, but without changing the cell's format (this is because, if I did, the next time I pulled the data it won't show).
I've added a column for Time only (using MOD(1,1)) like this -

CreateDt Time Output
8/29/20 7:19 AM 7:19:35 AM 1
8/29/20 7:19 AM 7:25:09 AM 2
8/29/20 7:25 AM 7:25:09 AM 3
8/29/20 7:25 AM 7:25:09 AM 4
8/29/20 7:25 AM 7:25:09 AM 5
8/29/20 7:25 AM 7:25:09 AM 6
8/29/20 7:25 AM 7:25:09 AM 7
8/29/20 7:25 AM 7:30:33 AM 8
8/29/20 7:30 AM 7:30:33 AM 9
8/29/20 7:30 AM 7:30:33 AM 10
8/29/20 7:30 AM 7:30:33 AM 11
8/29/20 7:30 AM 7:30:33 AM 12
8/29/20 7:30 AM 7:30:33 AM 13
8/29/20 7:30 AM 7:35:53 AM 14

And use this formula COUNTIFS(Sheet1!$G:$G,">=" &TIME(7,0,0),Sheet1!$G:$G,"<" &TIME(7,30,0)). Seems to work...

But I am looking to simplified it even further and remove the Time Column.
Can anyone help?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
My formula doesn't reference the sheet.
One more question, are the values that are being looked excel serial times or text strings?
 
Upvote 0
Also, my formula needs to be confirmed using the keys Contr-Shift-Enter (not only Enter); did you remember this when tried it?
Also, you have to refer to the column with the original data & time, I guess this is not column G
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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