How do Count a the number of times a date range appears within a date range!?

Discussion in 'Microsoft Excel Discussion' started by leelondon, Sep 27, 2006.

  1. leelondon

    leelondon Guest

    I'm stumped, I have no real idea on how to do this so any help much
    appreciated!

    I have a start date (no year ie, 1st December)
    and an end date (again no year say 5th Jan),

    And a date range, eg 11th May 2001 to 1 January 2006

    for info late:
    1st Dec to 5th Jan = 35 days

    How do i get a count of the number of 1st Decemebers to 5th Januarys in
    that date range
    for example

    1st December 2001 to 5th January 2002 is 1st
    1st December 2002 to 5th January 2003 is 2nd
    1st December 2003 to 5th January 2004 is 3rd
    1st December 2004 to 5th January 2005 is 4th

    1st December 2005 to 1th January 2006 is 4.8857 4+(31/35)

    So there are 4.8857 1st Dec - 5th Januarys! But how do i get a formula
    or script to do this, preferable a formula as i need to use the result
    in another formula

    If it helps what i am ultimately trying to do is calculate an average
    weekly sales figure for many different products. but exclude the 5
    week period beginning 1st Dec.

    Existing formumal is
    =Prod1Sales/(DAYS360(Prod1LaunchDate,ReportingDate)/7)

    =Prod1Sales/((DAYS360(Prod1LaunchDate,ReportingDate)/7)-NoDecJans*35)

    Where NoDecJan is the formula i cant work out for the number of Dec-Jan
    periods, and the 35 is the 35days in the period.

    Have i over complicated this is there a simple way?

    Any help much appreciated.
     
    leelondon, Sep 27, 2006
    #1
    1. Advertisements

  2. leelondon

    Micah Guest

    You may get farther by using the NETWORDAYS() function. The third
    (optional) parameter in that function allows you to specify a range.
    In that range, you have all the dates that are EXCLUDED from the "work
    calendar." If you defined in that range the list of dates from
    01DEC-05JAN, then maybe that would help. However, these dates need
    years as well, so you'd have to be careful that as years go by in your
    analysis, that your defined non-work days kept up. But you could
    define those dates pretty easily by using the DATE() formula, which
    would reference a base year, and then it would auto-update. Anyway,
    give that some thought.

    Micah

    leelondon wrote:
    > I'm stumped, I have no real idea on how to do this so any help much
    > appreciated!
    >
    > I have a start date (no year ie, 1st December)
    > and an end date (again no year say 5th Jan),
    >
    > And a date range, eg 11th May 2001 to 1 January 2006
    >
    > for info late:
    > 1st Dec to 5th Jan = 35 days
    >
    > How do i get a count of the number of 1st Decemebers to 5th Januarys in
    > that date range
    > for example
    >
    > 1st December 2001 to 5th January 2002 is 1st
    > 1st December 2002 to 5th January 2003 is 2nd
    > 1st December 2003 to 5th January 2004 is 3rd
    > 1st December 2004 to 5th January 2005 is 4th
    >
    > 1st December 2005 to 1th January 2006 is 4.8857 4+(31/35)
    >
    > So there are 4.8857 1st Dec - 5th Januarys! But how do i get a formula
    > or script to do this, preferable a formula as i need to use the result
    > in another formula
    >
    > If it helps what i am ultimately trying to do is calculate an average
    > weekly sales figure for many different products. but exclude the 5
    > week period beginning 1st Dec.
    >
    > Existing formumal is
    > =Prod1Sales/(DAYS360(Prod1LaunchDate,ReportingDate)/7)
    >
    > =Prod1Sales/((DAYS360(Prod1LaunchDate,ReportingDate)/7)-NoDecJans*35)
    >
    > Where NoDecJan is the formula i cant work out for the number of Dec-Jan
    > periods, and the 35 is the 35days in the period.
    >
    > Have i over complicated this is there a simple way?
    >
    > Any help much appreciated.
     
    Micah, Sep 27, 2006
    #2
    1. Advertisements

  3. leelondon

    Micah Guest

    You may get farther by using the NETWORKDAYS() function. The third
    (optional) parameter in that function allows you to specify a range.
    In that range, you have all the dates that are EXCLUDED from the "work
    calendar." If you defined in that range the list of dates from
    01DEC-05JAN, then maybe that would help. However, these dates need
    years as well, so you'd have to be careful that as years go by in your
    analysis, that your defined non-work days kept up. But you could
    define those dates pretty easily by using the DATE() formula, which
    would reference a base year, and then it would auto-update. Anyway,
    give that some thought.

    Micah

    leelondon wrote:
    > I'm stumped, I have no real idea on how to do this so any help much
    > appreciated!
    >
    > I have a start date (no year ie, 1st December)
    > and an end date (again no year say 5th Jan),
    >
    > And a date range, eg 11th May 2001 to 1 January 2006
    >
    > for info late:
    > 1st Dec to 5th Jan = 35 days
    >
    > How do i get a count of the number of 1st Decemebers to 5th Januarys in
    > that date range
    > for example
    >
    > 1st December 2001 to 5th January 2002 is 1st
    > 1st December 2002 to 5th January 2003 is 2nd
    > 1st December 2003 to 5th January 2004 is 3rd
    > 1st December 2004 to 5th January 2005 is 4th
    >
    > 1st December 2005 to 1th January 2006 is 4.8857 4+(31/35)
    >
    > So there are 4.8857 1st Dec - 5th Januarys! But how do i get a formula
    > or script to do this, preferable a formula as i need to use the result
    > in another formula
    >
    > If it helps what i am ultimately trying to do is calculate an average
    > weekly sales figure for many different products. but exclude the 5
    > week period beginning 1st Dec.
    >
    > Existing formumal is
    > =Prod1Sales/(DAYS360(Prod1LaunchDate,ReportingDate)/7)
    >
    > =Prod1Sales/((DAYS360(Prod1LaunchDate,ReportingDate)/7)-NoDecJans*35)
    >
    > Where NoDecJan is the formula i cant work out for the number of Dec-Jan
    > periods, and the 35 is the 35days in the period.
    >
    > Have i over complicated this is there a simple way?
    >
    > Any help much appreciated.
     
    Micah, Sep 27, 2006
    #3
  4. leelondon

    Micah Guest

    You may get farther by using the NETWORKDAYS() function. The third
    (optional) parameter in that function allows you to specify a range.
    In that range, you have all the dates that are EXCLUDED from the "work
    calendar." If you defined in that range the list of dates from
    01DEC-05JAN, then maybe that would help. However, these dates need
    years as well, so you'd have to be careful that as years go by in your
    analysis, that your defined non-work days kept up. But you could
    define those dates pretty easily by using the DATE() formula, which
    would reference a base year, and then it would auto-update. Anyway,
    give that some thought.

    Micah

    leelondon wrote:
    > I'm stumped, I have no real idea on how to do this so any help much
    > appreciated!
    >
    > I have a start date (no year ie, 1st December)
    > and an end date (again no year say 5th Jan),
    >
    > And a date range, eg 11th May 2001 to 1 January 2006
    >
    > for info late:
    > 1st Dec to 5th Jan = 35 days
    >
    > How do i get a count of the number of 1st Decemebers to 5th Januarys in
    > that date range
    > for example
    >
    > 1st December 2001 to 5th January 2002 is 1st
    > 1st December 2002 to 5th January 2003 is 2nd
    > 1st December 2003 to 5th January 2004 is 3rd
    > 1st December 2004 to 5th January 2005 is 4th
    >
    > 1st December 2005 to 1th January 2006 is 4.8857 4+(31/35)
    >
    > So there are 4.8857 1st Dec - 5th Januarys! But how do i get a formula
    > or script to do this, preferable a formula as i need to use the result
    > in another formula
    >
    > If it helps what i am ultimately trying to do is calculate an average
    > weekly sales figure for many different products. but exclude the 5
    > week period beginning 1st Dec.
    >
    > Existing formumal is
    > =Prod1Sales/(DAYS360(Prod1LaunchDate,ReportingDate)/7)
    >
    > =Prod1Sales/((DAYS360(Prod1LaunchDate,ReportingDate)/7)-NoDecJans*35)
    >
    > Where NoDecJan is the formula i cant work out for the number of Dec-Jan
    > periods, and the 35 is the 35days in the period.
    >
    > Have i over complicated this is there a simple way?
    >
    > Any help much appreciated.
     
    Micah, Sep 27, 2006
    #4
  5. leelondon

    ExcelJockey Guest

    re:How do Count a the number of times a date range appears with

    days360 defines a specific algorithm for normalizing months, a
    defined on the follow page

    http://office.microsoft.com/en-ca/assistance/ha011609841033.asp

    so first make sure you're using the right algorithm. if you're base
    in Europe, you might need to set the third parameter of DAYS360() t
    TRUE to use the European method

    NETWORKDAYS() counts working days so it's a different animal, thoug
    the idea of excluded dates is what's needed here.

    i would use the algorithm to count days whenever possible. fo
    instance, DAYS360("12/1/2005", "1/5/2006") = 34
    not 35. (american style dates MM/DD/YYYY

    i would substract from DAYS360(Launch, reporting) the number of WHOL
    35 day periods, ie WHOLE periods * 35, then subtract DAYS360(X,Y) fo
    X and Y between 12/1/AAAA and 1/5/BBBB, ie use the DAYS360 function
    then finally divide by 7 to get week averages

    (a recursive function could work nicely here...

    -----------------------------------------------------------------------
    ExcelJockey's profile: http://www.exceljockeys.com/forums/profile.php?mode=viewprofile&u=
    View this thread: http://www.exceljockeys.com/forums/viewtopic.php?t=30772
     
    ExcelJockey, Sep 28, 2006
    #5
  6. leelondon

    leelondon Guest

    Re: re:How do Count a the number of times a date range appears with

    Thanks everyone, I will give it some thought, appreciate all your help.
     
    leelondon, Oct 4, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Matlock

    How to count the number of values that fall within a range

    Steve Matlock, Jan 22, 2004, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    326
    Piotr Glenszczyk
    Jan 22, 2004
  2. mmay321

    Count number of cells that fall within a range of dates

    mmay321, Aug 8, 2005, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    386
    mmay321
    Aug 8, 2005
  3. Fester

    Count the number of incidents within a certain date range

    Fester, Jan 4, 2006, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    191
    Ron Rosenfeld
    Jan 4, 2006
  4. jbesr1230

    count the number of new lows within a range of cells

    jbesr1230, Jun 1, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    137
    aresen
    Jun 1, 2006
  5. Paul Hyett
    Replies:
    0
    Views:
    240
    Paul Hyett
    Jul 19, 2012
Loading...

Share This Page