+ Reply to Thread
Results 1 to 7 of 7

COUNTIFS formula for reading date/time stamps

  1. #1
    Registered User
    Join Date
    09-26-2008
    Location
    San Jose, CA
    Posts
    11

    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. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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?
    Attached Files Attached Files
    Last edited by ChemistB; 10-30-2014 at 01:39 PM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-26-2008
    Location
    San Jose, CA
    Posts
    11

    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. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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. #6
    Registered User
    Join Date
    09-26-2008
    Location
    San Jose, CA
    Posts
    11

    Re: COUNTIFS formula for reading date/time stamps

    Quote Originally Posted by ChemistB View Post
    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. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: COUNTIFS formula for reading date/time stamps

    No worries, glad we got it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Fliter time stamps within criteria for COUNTIFS function
    By klemke89 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2013, 07:17 PM
  2. Formula to calculate date and time stamps + lunch
    By excelgeezer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2013, 05:35 AM
  3. Excel 2007 : Difference between two Date Time Stamps
    By Sedlacek2001 in forum Excel General
    Replies: 3
    Last Post: 05-15-2012, 11:12 AM
  4. Time Difference in hours between two date time stamps
    By Cipher in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2010, 10:24 AM
  5. Difference between two Excel Date/Time Stamps
    By lnapier in forum Excel Formulas & Functions
    Replies: 51
    Last Post: 09-06-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1