+ Reply to Thread
Results 1 to 11 of 11

Count number of cells with specific date and time range

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    38

    Count number of cells with specific date and time range

    I have a spreadsheet with raw data, D column has dates, E column has times.

    D2 - 9/25/2013
    D3 - 9/25/2013
    D4 - 9/24/2013
    D5 - 9/23/2013

    E2 - 21:53
    E3 - 21:48
    E4 - 8:12
    E5 - 10:46

    I would like to have a formula to put in column F that would count how many cells have 9/25/2013 in D as a date and time in E column that has a time between 21:00 - 21:59.

    Would this be possible?

    Thanks,

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Count number of cells with specific date and time range

    Use

    =COUNTIFS(D:D,DATE(2013,9,25),E:E,">="&TIME(21,0,0),E:E,"<="&TIME(21,59,0))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Count number of cells with specific date and time range

    =SUMPRODUCT(IF(D2:D5="9/25/2013",1,0)*IF(E2:E5>=0.875,1,0)*IF(E2:E5<=0.91667,1,0))

    it's an array formula so needs to be confirmed with ctrl+shift+enter.

    Edit: or just use Ace's formula.

  4. #4
    Registered User
    Join Date
    04-19-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Count number of cells with specific date and time range

    Thanks for the answers, I've tried both of these and they are both returning a 0. When I know they should have a count. Any suggestions?

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Count number of cells with specific date and time range

    Make sure the dates are actually dates and not text. Ditto with the time.

    Do this
    - In a separate cell input 1
    - Copy this cell
    - Select your data ranges
    - Paste Special -- Multiply -- values
    - Ok

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count number of cells with specific date and time range

    Try this:

    Copy and paste formula below

    =COUNTIFS($D$2:$D$5,"9/25/2013",$E$2:$E$5,">21:00",$E$2:$E$5,"<21:59")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    04-19-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Count number of cells with specific date and time range

    Sorry guys I still cannot get it, I'm probably over my head here anyways. I've attached the file, the columns are changed but I believe it still should work. Could someone take a look for me?

    Chat Data-2.xlsx

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count number of cells with specific date and time range

    Paste in C2:

    =COUNTIFS(A:A,"9/23/2013",B:B,">21:00",B:B,"<21:59")

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count number of cells with specific date and time range

    Your dates appear to be stored in column A as Text Strings, not real dates.

    Try this
    Copy a blank cell
    Highlight column A
    Right click - Paste Special - Values - Add
    OK

  10. #10
    Registered User
    Join Date
    04-19-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Count number of cells with specific date and time range

    Thank you for all your help everyone, I ended up putting a 0 in front of the date 9/23/2013 in the formula and it works! Thanks again all!

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count number of cells with specific date and time range

    Instead of adding 0 all you had to do is to fix your date format.

+ 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. Count Specific Number Across Range Of Cells Across Multiple Tabs
    By PacE in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-23-2012, 09:43 AM
  2. Count of cells between range of time, per date
    By hughdepayens in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-03-2011, 04:18 PM
  3. Counting number of occurences specific date in range of cells in a table
    By jzzman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2009, 01:47 AM
  4. [SOLVED] count number occurring within specific date range
    By Biff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2006, 01:40 PM
  5. count number occurring within specific date range
    By Ducky in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-11-2006, 01:35 PM

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