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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
My formula doesn't reference the sheet.
One more question, are the values that are being looked excel serial times or text strings?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,941
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
 

shondown

New Member
Joined
Aug 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
My formula doesn't reference the sheet.
One more question, are the values that are being looked excel serial times or text strings?

It is Excel date, which is why I had to use MOD to pull out the time.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top