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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Anthony47

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

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
117
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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*")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
If your Date/Time formula is in column D

=SUMPRODUCT(--(HOUR(D1:D1000)=7)*(MINUTE(D1:D1000<30))
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,918

ADVERTISEMENT

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
 

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
117
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

shondown

New Member
Joined
Aug 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
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.
 

shondown

New Member
Joined
Aug 29, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top