PC Review


Reply
Thread Tools Rate Thread

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

 
 
leelondon
Guest
Posts: n/a
 
      27th Sep 2006
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.

 
Reply With Quote
 
 
 
 
Micah
Guest
Posts: n/a
 
      27th Sep 2006
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.


 
Reply With Quote
 
 
 
 
Micah
Guest
Posts: n/a
 
      27th Sep 2006
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.


 
Reply With Quote
 
Micah
Guest
Posts: n/a
 
      27th Sep 2006
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.


 
Reply With Quote
 
ExcelJockey
Guest
Posts: n/a
 
      28th Sep 2006
days360 defines a specific algorithm for normalizing months, a
defined on the follow page

http://office.microsoft.com/en-ca/as...1609841033.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/p...viewprofile&u=
View this thread: http://www.exceljockeys.com/forums/v...ic.php?t=30772

 
Reply With Quote
 
leelondon
Guest
Posts: n/a
 
      4th Oct 2006
Thanks everyone, I will give it some thought, appreciate all your help.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the number of times a specific date appears in a range of ce Louisa Microsoft Excel Worksheet Functions 6 19th May 2009 05:26 PM
Count the number of times a specific date appears in a range of ce Louisa Microsoft Excel Worksheet Functions 0 19th May 2009 11:50 AM
Need to Count the number of times a value occurs within a dt range Gina Microsoft Excel Worksheet Functions 9 4th Jul 2008 10:19 PM
Counting the number times a record appears within a date period =?Utf-8?B?UmljNDA0?= Microsoft Access Queries 4 21st Feb 2007 10:38 PM
Count the number of times a cell value is within a specific range =?Utf-8?B?RXZlcmV0dA==?= Microsoft Excel Worksheet Functions 4 2nd Sep 2006 10:54 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:59 PM.