CODEWITHSUNDEEP
excel
Mark Banin
Mark Banin

Reputation: 21

Excel: Counting how many rows fall within a time period

I have an excel sheet with 2 columns

Column A = "CA", "CR" or "IN"

Column B = Date & time format DD/MM/YYYY HH:MM

I want to make a count at the bottom of a column for each row that has this criteria:

i) Row 1-8 = "CA"

ii) Row 1-8 needs to check for a time range, namely > "17:00" and < "04:59"

This is what I've come up with so far:

=COUNTIFS(A2:A8,"CA",B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)>"04:59"), B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)<"17:00")

I presume using a range within a the text function is wrong, but don't know how to resolve this.

Because Column B is in a date and time format, I'm having to change it to a string within the function so I can make a test on just the time - Maybe there's a better way?)

Thanks

Upvotes: 1

Views: 2139

Answers (3)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You do not need to deal with strings at all for the time. Excel provides with quite a few date&time functions, enough in your case.

A concise formula is

=SUMPRODUCT((A1:A8="CA")*(MOD(B1:B8,1)<=TIME(17,0,0))*(MOD(B1:B8,1)>TIME(4,59,0)))

Notes:

  1. I assume you meant <=17:00, instead of <17:00. It is easy to modify the formula if I was wrong.
  2. You used A2:A8, but you probably wanted A1:A8, as per the text. It is easy to modify the formula if I was wrong.
  3. COUNTIFS is usually much less flexible than SUMPRODUCT (combined with other functions as MATCH, INDEX, SUM, etc.)

PS: as barry houdini points out, the OP asks for two opposite time ranges. I have chosen here one of them.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

This is difficult to do with COUNTIFS because you can't modify ranges with functions.....but you can do that with SUMPRODUCT - try this

=SUMPRODUCT((A2:A8="CA")*(MOD(B2:B8,1)<"17:00"+0)*(MOD(B2:B8,1)>"04:59"+0))

I'm assuming that you want between 04:59 and 17:00 - in your point ii) you show it the opposite way to what you have in your formula

MOD extracts the time from the date/time so it can be compared against a time range. If you are counting within a range including whole hours, e.g. 5 to 16 inclusive you can use HOUR function without MOD, i.e.

=SUMPRODUCT((A2:A8="CA")*(HOUR(B2:B8)>=5)*(HOUR(B2:B8)<=16))

Upvotes: 1

Jerry
Jerry

Reputation: 71538

The problem with COUNTIFS is that you can't manipulate the conditions a lot. You could insert a column containing the time only, in which case you could use COUNTIFS but if you cannot, you can use SUMPRODUCT for substitute:

=SUMPRODUCT((A1:A6="CA")*(((TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0))+(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)))>0))

This applies a few conditions on an example range A1:B6:

(A1:A6="CA") that ensures that the row has CA,

(TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0)) that ensures that the time is above 17:00

(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)) that ensures that the time is before 05:00 (You have 4:59 in your question, if you really meant less than 4:59 then change this part).

The + for the last two conditions 'OR's the two conditions, then the whole thing is 'AND'ed with the first condition.

Upvotes: 3

Related Questions