COUNTIFS with date time format

[svendfj]

Please let me ask for guidance on use of COUNTIFS where the data is ''Date + Time' format; specifically: "1/4/2022 12:00:00 AM"

The attached image shows:
A) A simple working COUNTIFS statement with integers. A user enters desired numbers in cells B2 and B3 and the corresponding result is displayed in cell B4. In this example, the user is asking 'how many fields in the data are at least 2 and no more than 5. Since this matches 4 of the 6 fields, the number 4 is displayed in B4.
B) A non-working adaptation of the above solution, with the one difference being that we are working with 'Date + Time' fields instead of integers.
C) An adaptation of 'B', that also does not work, but is attempting to take the user entries in B30 and B31 and use cells B28 and B29 to convert them for use in the COUNTIFS formula.

This is a simplified version and the actual finished version will be used against a large amount of data in a separate worksheet and I do not have the option of making appropriate conversions on that worksheet.

Thanks in advance for any guidance you can lend.

Sven

 

[Leo Skywalker]

Hi there,

You can use:

B17

=SUMPRODUCT((B19:B24>=B15)*(B19:B24<=B16))

 

[svendfj]

Leo, Thank you very much.

I have been working with your solution, but have not yet been successful. The actual data (not my sample data) is formatted as Date + Time (#####.#####), while the user entered fields B15 and B16 are just Date (#####).

I will look at it some more.

Thanks again.

 

[Leo Skywalker]

Leo, Thank you very much.

I have been working with your solution, but have not yet been successful. The actual data (not my sample data) is formatted as Date + Time (#####.#####), while the user entered fields B15 and B16 are just Date (#####).

I will look at it some more.

Thanks again.

Looks like your dates + time are wrong.

Your corversion to date + times uses B16 + A14, and shows a Value error.
Also, if you have in B16 a reference to B17, then the formula is wrong there, too.

Please check your dates and times or send a saple file.

 

[svendfj]

Leo, Thank you very much. I worked with the formatting some more based on your original suggestion of using SUMPRODUCT and got it to work. I have some additional functionality that I worked through, but your original post is the answer. The work that I started on the third version, 'C', is not needed.

Thanks again!

 

[Leo Skywalker]

You're welcome!, glad I could help!