# COUNTIFS formula for reading date/time stamps

1. ## COUNTIFS formula for reading date/time stamps

Hello all,

I'm trying to create a formula that will count the number of occurrences that fall within a specific date/time from a range of data. I have a named range (OrdersRecd) of dates and times with each field reading as shown here: "10/24/2014 9:27:51 AM"

Then I have a table with dates ("10/24/2014", etc) along the top (y-axis) and times (6:00:00 to 23:00:00) along the side (x-axis). The count corresponding with each date/time will fill the

I've come up with a formula that works, but isn't dynamic (doesn't use field references) and so would require rewriting for each field. The formula:
``Please Login or Register  to view this content.``
My attempts so far in creating a dynamic formula have been unsuccessful. Here's what I've come up with:
``Please Login or Register  to view this content.``
I believe I understand how the logic is off, but I don't know how to go about resolving it. Any help would be greatly appreciated.

--Greg

2. ## Re: COUNTIFS formula for reading date/time stamps

Lets say your times are in D2:J2 and your Dates are in C3:C100

In D3 copied across and down

=COUNTIFS(OrdersRecd, ">=" & \$C2 + D\$3, OrderRecd, "<"& \$C2+ E\$3)
Does that make sense to you?

3. ## Re: COUNTIFS formula for reading date/time stamps

ChemistB,

Thanks for the quick response! Yes, that makes perfect sense. I should have seen that. Unfortunately, it doesn't appear to work when I input it into the spreadsheet.

As there's no proprietary data, I'm going to attempt to u/l a copy of it. It could be there's something else going on that I'm not seeing. A cell format issue, perhaps? I'm not sure.

Hourly Incoming Order Log.xlsm

4. ## Re: COUNTIFS formula for reading date/time stamps

Not sure what you were doing but you had several different versions of COUNTIFs in your cells. I copied and pasted the correct one and things seem to be working properly now.

5. ## Re: COUNTIFS formula for reading date/time stamps

Oh, because the formula uses the next column to determine upper limit of time, you'll need to put a 1 into cell T5 to cover values between 23:00 and midnight.

6. ## Re: COUNTIFS formula for reading date/time stamps

Originally Posted by ChemistB
Not sure what you were doing but you had several different versions of COUNTIFs in your cells. I copied and pasted the correct one and things seem to be working properly now.
I had only made the change to cell C6 (which I neglected to mention) and was apparently so mired in tunnel-vision that I didn't think that the value of "0" that came up was legitimate. Thanks for the second set of eyes.

7. ## Re: COUNTIFS formula for reading date/time stamps

No worries, glad we got it working.

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