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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try the following array formula:
Code:
=SUM(--(MOD(Sheet1!$G$1:$G$1000,1)>=7/24)*MOD(Sheet1!$G$1:$G$1000,1)<=7.5/24))

If you know you'll have more than 1000 lines, then change those "1000" to a more appropriate value, but yo cannot say G:G (ie the whole column)
7/24 is a way for creating the time 7:00; 7.5/24 creates the time 7:30; if you need different times range then you need to adapt that portion; for example 7.25/24 create the time 7:15

It is an "array formula", so you have to confirm it using the combination of keys Contr-Shift-Enter

Bye
 
Upvote 0
Hey shondown,

Try the following formula, it will search for cells within column G on Sheet 1 which are either equal to 7:00 or less than 7:30. If this is the only sheet then you can then also remove the Sheet1! portion as well.

=COUNTIFS(Sheet1!$G:$G,,"=*7:00*",Sheet1!$G:$G,">*7:3*")
 
Upvote 0
If your Date/Time formula is in column D

=SUMPRODUCT(--(HOUR(D1:D1000)=7)*(MINUTE(D1:D1000<30))
 
Upvote 0
Anthony47 said:
Try the following array formula:
Code:
=SUM(--(MOD(Sheet1!$G$1:$G$1000,1)>=7/24)*MOD(Sheet1!$G$1:$G$1000,1)<=7.5/24))
In this formula you have to use the column with the original Date&Time stamp; probably it is not G

And don't miss the messages from @t0ny84 and @mikerickson, above
 
Upvote 0
Apologies I didn't fully read the question and I missed the 10 minute edit time frame.

=COUNTIFS(Sheet1!$G:$G,"=*7:*",Sheet1!$G:$G,">*7:3*")

t0ny84
 
Upvote 0
Hi guys,
=SUM(--(MOD(Sheet1!$G$1:$G$1000,1)>=7/24)*MOD(Sheet1!$G$1:$G$1000,1)<=7.5/24)) this ones returns 0

=COUNTIFS(Sheet1!$G:$G,"=*7:*",Sheet1!$G:$G,">*7:3*") so is this one.

=SUMPRODUCT(--(HOUR(D1:D1000)=7)*(MINUTE(D1:D1000<30)) and returns 0 also.

I am doing the formula on another sheet, if that would mean anything.
Is there anything wrong with the numbers formatting?
 
Upvote 0
If your formula isn't on the sheet where the data is, the sheet name would have to be included in the addresses in the formula.
 
Upvote 0
If your formula isn't on the sheet where the data is, the sheet name would have to be included in the addresses in the formula.
Yep, the raw data is on Sheet 1, and I'm putting this formula in Sheet 2
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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