Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "X"

1. Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "X"

Hello people!

First time posting here, and I've hit a bit of a hard wall.
I have a column containing days of the week Mon-Sun, and another column containing specific times of when a particular event occurs.

e.g.
Column A | Column B
Tuesday | 15:23
Thursday | 10:18
Wednesday | 21:46
Tuesday | 15:02
Monday | 03:15
Friday | 19:08
Tuesday | 09:51

What I'm trying to generate is a formula that can look at both column A and column B, and count how many times on a Tuesday did the event take place between 15:00 and 15:59, (in the above example the outcome of this sought-after formula would be 2), and I don't know how to do it

Any help would be thoroughly appreciated!

Thank you so much

2. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

One way is this:=COUNTIFS(\$A\$1:\$A\$7,"Tuesday",\$B\$1:\$B\$7,">="&0.625,\$B\$1:\$B\$7,"<="&0.6659)
Times in excel are numbers converted. meaning that in the formula above 0.625 is 15:00 and 0.6659 is 15.59

3. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

You could also write it like this if it's easier to follow

=COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&((1/24)*15),B1:B7,"<"&((1/24)*16))

4. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

OMG it worked! Thank you so much !!!

Could you explain how the times are converted into numbers?

5. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

My formula makes it a bit explicit, but times in excel as stored as decimals. One day is 1. Therefore 1/24 is one hour.

6. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

Thank you both!

7. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

The values can obviously be in 2 cells

so if c1 contains 15:00
and c2 contains 16:00

=COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&((1/24)*15),B1:B7,"<"&((1/24)*16))

becomes

=COUNTIFS(A1:A7,"Tuesday",B1:B7,">="&c1,B1:B7,"<"&c2)

8. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

Ah yes ofc! Makes so much sense, thank you!

9. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

Originally Posted by Kyle123
My formula makes it a bit explicit, but times in excel as stored as decimals. One day is 1. Therefore 1/24 is one hour.
in the same way that excel stores times as decimals, does it do something similar with dates?

for example, if I wanted to include the date 12/05/2019 in a this formula, what would it have to be written as?
=IF(ISNUMBER(SEARCH("12/05/2019",E2)),"first delivery"," ")

because this keeps giving me the value_if_false in the cell.

10. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

This is a pretty good explanation of how Excel stores dates and times: http://www.cpearson.com/Excel/datetime.htm

11. Re: Countif the values are betw 00:00 - 00:59 (hours) when another column contains value "

Amazing thank you so much!

There are currently 1 users browsing this thread. (0 members and 1 guests)