1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

countif excel between two dates

Discussion in 'Business Applications' started by apickrell, Aug 19, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. apickrell

    apickrell Thread Starter

    Joined:
    Aug 19, 2008
    Messages:
    5
    I am having trouble figuring out how to write the correct function using countif. I have two columns. One column has the start date and the other column has the end date. I want a separate cell to countif those two dates are between the specified dates.

    start date end date
    jane doe 1/22/2008 4/3/2008

    February ?

    So for February I need to countif those two dates fall between 2/1/2008 and 2/28/2008.
    Please help.
     
  2. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Hi, welcome to TSG. :)

    There's probably a cleaner way to do this and I'm sure someone will take a look at it soon, but see my attached spreadsheet for what I did. Basically, I created an extra column that says "yes" if the condition is true, and "no" if it's not. My If statement looks at the start date and determines whether it's earlier than 2/1/08. If so, the answer is no. If not, it looks at the start date AND the end date to determine whether they're both earlier than 2/28/08. If so, the answer is yes. Note: this only works if you assume the end date is always going to be the same day or later than the start date. The Countif function in C11 just counts the number of yeses. You could put that function in another column and hide the yes/no column if you needed to. You could also hide the rows that specify 2/1/08 and 2/28/08, but if you're looking for different date ranges at different times, it could come in handy. Let me know if that didn't make any sense. :eek: Like I said, I'm sure someone will have a cleaner answer soon.
     

    Attached Files:

    • tsg.xls
      File size:
      13.5 KB
      Views:
      1,829
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You could use a shorter version of Wendy's formula:

    =AND(A2>=$B$11,B2<=$B$12)

    (returns FALSE/TRUE)
     
  4. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Uh, yeah, or you could do that. :eek: Duh. I KNEW I made it overly complicated! Thank you, sir. (y)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Or, if you'd prefer to avoid the helper column:

    =SUMPRODUCT((A2:A6>=B11)*(B2:B6<=B12))
     
  6. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Well now you're just showing off. :p
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hey, we've ALL done it! :D
     
  8. apickrell

    apickrell Thread Starter

    Joined:
    Aug 19, 2008
    Messages:
    5
    this helps but maybe I did not explain myself well because the first set of dates should have been yes also. basically these are start and end dates of contracts and I want to know below where it states February, if the above dates overlap the dates in February.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Overlap" is ambiguous.

    From your first post:

    "I want a separate cell to count(if) those two dates are between the specified dates."

    If the month is February, that makes the specified dates 1st Feb 2008 and 29th Feb 2008. Since neither 22nd Jan nor 3rd April are between 1st Feb and 29th Feb, I'm afraid you've lost me (and anyone else, possibly).
     
  10. apickrell

    apickrell Thread Starter

    Joined:
    Aug 19, 2008
    Messages:
    5
    any february date does fall between jan 22 and april 4th. I need the start and end date to read as a range of dates. which in turn february dates again would fall in that range.
     
  11. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    So you need to know whether your specified date range falls between the start and end dates? Sorry, that's the opposite of what you said in your first post, so you basically just need the opposite of Andy's formula. But I'll let him say for sure, since who knows how complicated I might manage to make it! :)
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Agreed.

    apickrell, see the "Qualified v2" formulas in E:E and the SUMPRODUCT "variation" in C12 of the attached (an update of Wendy's "template"). HTH :)
     

    Attached Files:

    • tsg.xls
      File size:
      14.5 KB
      Views:
      823
  13. apickrell

    apickrell Thread Starter

    Joined:
    Aug 19, 2008
    Messages:
    5
    I think that I am really confusing you on how I state this. Let me try again. so from Feb 1 to Feb 29, if any of those dates or between those dates falls anywhere between the start and end date it needs to count as one. So if you have 1/22/2008 to 4/2/2008 as your start and end date then the answer would be yes because any date in feb would fall between those two dates. Does this help any.
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    That's what the formula in E7 of the latest attachment does; result = TRUE. Did you check it?
     
  15. apickrell

    apickrell Thread Starter

    Joined:
    Aug 19, 2008
    Messages:
    5
    I see but the answer for b12 should be 3 because 3 of those above start and end dates have a feb date in their range.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/741646

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice