Countifs function to count values done today on a particular hour

Hi Everyone,
im trying to count the number of records that were done today at 10am. The dates in column J are in the format 2/26/2016 10:02:00 AM. i got part of it working using code below however not able to figure out how to get count if date is today and the hour is 10

Please Login or Register  to view this content.

Thank you for the help

Originally Posted by 6StringJazzer

I haven't tested this but I think this is what you need. I could test it if you provide your file.

Please Login or Register  to view this content.

Hey, thanks for this but i get result FALSE

here is an example sheet

Originally Posted by 6StringJazzer

I don't know how you could get FALSE because COUNTIFS returns either an integer or an error.

The file you attached doesn't match the formula you are using. If I modify the code to match your file then it works fine. See attached for a version that uses a formula, plus the version that uses the code I provided.

Thanks 6StringJazzer ...works like a charm but i am having a hard time understanding how your vb code is working. can you please explain?
Thanks

The code is just a direct translation from the formula that appears in E3. First, the formula, because I'll bet you are having a hard time understanding the formula. Once you understand that the code is not that hard.

=COUNTIFS(A:A, "Information",B:B, ">="&TODAY()+10/24,B:B,"<"&TODAY()+11/24)

You already decided to use COUNTIFS so I assume you are familiar with how it it works--it's a series of pairs of where you want to count, and condition for when to count it. The key thing you have to know is that when there are multiple pairs of criteria, they must ALL be true to include an element in the count.

You already have the first one under control, counting rows that have "Information" in column A.

Next comes

B:B, ">="&TODAY()+10/24

This mean look in column B, but you already knew that. Then we want to find all cells that have a date of today and a time greater than or equal to 10 AM. If you just stick a value in this position, like

B:B, TODAY()

then Excel will check to see what cells in column B are equal to TODAY(). But you can also do other kinds of comparisons if you build a string to do it. The beginning of the string can be one of these: = < > <= >=. (People don't normally use "=" because using no symbol at all is understood to mean "=".) This is something that is well understood in the Excel user community but IMHO is poorly explained in the Excel help page.

So we start the string with ">=". Then we use "&" to concatenate it to the next part of the string. The next part is 10:00 AM today. So we start with TODAY(). TODAY() is a function that returns the current date. Excel stores date/time internally as a real number of the number of days that have passed since 12/31/1899. For example, today is 42426. That represents the time of 00:00, because it is an integer. If you want to add a particular time of day, you have to add a decimal portion that is the fraction of a day. 10 AM is 10 hours out of 24, so we add 10/24 to the string (you could also use 4.16666666666667 but if you use 10/24 you are letting Excel do the math for you and it's also easier to see that you mean 10 AM).

Once you understand that, it will be easier to see that

"<"&TODAY()+11/24

is checking for values less than 11 AM today. When you combine that check AND time greater than or equal to 10 AM, you get all the times in the 10:00 hour.

Now let's convert to VBA.

Please Login or Register  to view this content.

Looking at your VBA, I think you have it figured out how to move from formulas to VBA. Here we just do the same thing, concatenating ">=" and "<" to Date instead of TODAY(), and adding the fractions.

Does that help?

Originally Posted by 6StringJazzer

The code is just a direct translation from the formula that appears in E3. First, the formula, because I'll bet you are having a hard time understanding the formula. Once you understand that the code is not that hard.

=COUNTIFS(A:A, "Information",B:B, ">="&TODAY()+10/24,B:B,"<"&TODAY()+11/24)



You already decided to use COUNTIFS so I assume you are familiar with how it it works--it's a series of pairs of where you want to count, and condition for when to count it. The key thing you have to know is that when there are multiple pairs of criteria, they must ALL be true to include an element in the count.

You already have the first one under control, counting rows that have "Information" in column A.

Next comes

B:B, ">="&TODAY()+10/24

This mean look in column B, but you already knew that. Then we want to find all cells that have a date of today and a time greater than or equal to 10 AM. If you just stick a value in this position, like

B:B, TODAY()

then Excel will check to see what cells in column B are equal to TODAY(). But you can also do other kinds of comparisons if you build a string to do it. The beginning of the string can be one of these: = < > <= >=. (People don't normally use "=" because using no symbol at all is understood to mean "=".) This is something that is well understood in the Excel user community but IMHO is poorly explained in the Excel help page.

So we start the string with ">=". Then we use "&" to concatenate it to the next part of the string. The next part is 10:00 AM today. So we start with TODAY(). TODAY() is a function that returns the current date. Excel stores date/time internally as a real number of the number of days that have passed since 12/31/1899. For example, today is 42426. That represents the time of 00:00, because it is an integer. If you want to add a particular time of day, you have to add a decimal portion that is the fraction of a day. 10 AM is 10 hours out of 24, so we add 10/24 to the string (you could also use 4.16666666666667 but if you use 10/24 you are letting Excel do the math for you and it's also easier to see that you mean 10 AM).

Once you understand that, it will be easier to see that

"<"&TODAY()+11/24

is checking for values less than 11 AM today. When you combine that check AND time greater than or equal to 10 AM, you get all the times in the 10:00 hour.

Now let's convert to VBA.

Please Login or Register  to view this content.

Looking at your VBA, I think you have it figured out how to move from formulas to VBA. Here we just do the same thing, concatenating ">=" and "<" to Date instead of TODAY(), and adding the fractions.

Does that help?

This is perfect explanation. Thank u again

You're welcome, glad to help.

BTW, you don't have to hit Reply With Quote just to reply, you can just type a reply in the Quick Reply window. No need to quote unless there is something specific you want to point out. As you can see that quote takes up a lot of space in the thread.