+ Reply to Thread
Results 1 to 15 of 15

Count number of days between 2 dates

  1. #1
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Count number of days between 2 dates

    Hi, sorry my English, i´m Portuguese, so...hehe

    I have a question, does anyone now how to count between dates, for exemple :

    01-02-2009
    15-02-2009
    22-02-2009
    25-02-2009
    03-03-2009
    11-03-2009

    And i wont only to count between 01-02-2009 / 28-02-2009, the result it will be 4

    Thanks.

    Miguel
    Last edited by NBVC; 03-17-2009 at 01:03 PM. Reason: Revised title

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Date....

    Count in days? Just put - between dates you want... E.g. A3-A2

  3. #3
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    No, just the number of times that appears, for exemple :

    01-02-2009
    15-02-2009
    22-02-2009
    25-02-2009
    03-03-2009
    11-03-2009

    I only have 4 entries in february.....

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count number of days between 2 dates

    If you place the Min/Max dates in 2 cells, say D1 and D2

    then try:

    =COUNTIF(A1:A10,">="&D1)-COUNTIF(A1:A10,">"&D2)

    where A1:A10 contain your dates list. Adjust as necessary
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    01-24-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2003 &2007
    Posts
    27

    Re: Count number of days between 2 dates

    If you put the min date in say cell D2

    Then in E2 the formula:

    =SUMPRODUCT(($A$1:$A$10>=D2)*1,($A$1:$A$10<=EOMONTH(D2,0))*1)

    This give your answer. The range can be changed to suit.

    EllBol

  6. #6
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    Thank a lot, it works just fine.....

  7. #7
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    Sorry to bother, but i´ve another question, the formula works fine, but i want to ad another condition, for example:

    01-02-2009 A
    15-02-2009 A
    22-02-2009 A
    25-02-2009 N
    03-03-2009 N
    11-03-2009 N

    Count only february with A in the another column...

    Thanks

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count number of days between 2 dates

    something like:

    =Sumproduct(--(A1:A10>=D1),--(A1:A10<=D2),--(B1:B10="A"))

  9. #9
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    That formula is not working for me,

    http://spreadsheets.google.com/pub?k...MgjgwiJHA8SCJA

    Maybe you can see what I want. I want to the number of times that appears "A" between 01-02-2009 and 28-02-2009, the result will be 3

    Thanks

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count number of days between 2 dates

    So what's the exact formula you are using? What result did you get with that formula?

    The formula NBVC posted should work fine if you have the start date of the period in D1 and the end date in D2

  11. #11
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    That formula contains errors in my excell...

  12. #12
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count number of days between 2 dates

    Your excel version may require ; in place of , so try like this

    =Sumproduct(--(A1:A10>=D1);--(A1:A10<=D2);--(B1:B10="A"))

  13. #13
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    And if i use Dcount, it will work, if yes how can y use it??

  14. #14
    Registered User
    Join Date
    03-16-2009
    Location
    Évora
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Count number of days between 2 dates

    Quote Originally Posted by daddylonglegs View Post
    Your excel version may require ; in place of , so try like this

    =Sumproduct(--(A1:A10>=D1);--(A1:A10<=D2);--(B1:B10="A"))

    Y try like that, gives me 0, instead of 2

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count number of days between 2 dates

    What happens with this?

    =Sumproduct(--(A1:A10>=date(2009,2,1));--(A1:A10<=date(2009,2,28));--(B1:B10="A"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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