Finding weekends in date range 1

[Saint1234]

I am trying to find the number of weekend days specified by a date range (all saturdays and sundays with the date range). I am displaying the date range using the following Totext(Minimum({?Date Parameter}),"dd-MM-yyyy&quot + " to " + Totext(Maximum({?Date Parameter}),"dd-MM-yyyy&quot. I know I can get the day of the week number by CStr(Weekday({@formula})). But I can't get these two formulas to work together. I have tried many things but I think I am missing something really stupid. Thanks in advance.
G

 

[dgillz]

The following formula returns the number of sundays between Firstday and Lastday:

datevar firstday:=Date(2004,2,1);
datevar lastday:=date(2004,2,29);
numbervar loop;
numbervar Days;
numbervar span:=datediff("d",Firstday,LastDay);


For loop:= 0 to span do(
if dayofweek(firstday+loop) = 5 then Days:=Days+1 else Days:=Days);

Days

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Reebo99]

The following formula will count the number of Saturdays and Sundays within a date range :

Local DateVar Start := Date(2004,2,1);
Local DateVar End := date(2004,2,29);
DateDiff ("ww", Start, End, crSaturday) + DateDiff ("ww", Start, End, crSunday)

Replace the items in bold with your dates/fields. If you wanted to get the number of Fridays, simply change crSaturday to crFriday.

Hope this helps...

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Reebo99]

Dgillz - numbervar loop;

Isn't loop a reserved word?

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[dgillz]

Reebo99,

It is in VB syntax but not in crystal syntax.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Reebo99]

I've just tested in CR9 and loop is reserved in Crystal and Basic syntax.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[synapsevampire]

To add to this, consider creating a Periods table in your database to maintain meaningful information about every day of the year, such as business day, weekend, holiday, week, month, quarter, fiscal quarter, etc.

This is a standard data warehouse table and will simplify many requirements.

I have a FAQ here with a SQL Server script:

faq767-4532

This is especially useful for demonstrating periods that are not in a data set, but will also satisfy your requirements by joining your table to it via the date.

-k

 

[rosemaryl]

Dear Saint1234,

I am curious, if your date range begins on a weekend date, do you want that date considered as one of the weekends?

The reason I ask is because of an anomolay with the datediff function. This was discovered by Mbarron and myself some time ago and the back and forth on that can be found in thread149-304415

If you read the help on datediff, you will see that it states:

//begin quote:

Use DateDiff with the "ww" parameter to calculate the number of firstDayOfWeek's occurring between two dates. For the DateDiff function, the "ww" parameter is the only one that makes use of the firstDayOfWeek argument... For example, if firstDayOfWeek is crWednesday, it counts the number of Wednesday's between startDateTime and endDateTime. It does not count startDateTime even if startDateTime falls on a Wednesday, but it does count endDateTime if endDateTime falls on a Wednesday...
//end quote

So keeping the above in mind, how could we solve this?

The following formula adds a day as appropriate when the start date is on a weekend.

//Begin formula
Local DateVar Start := Date(2004,2,7);
Local DateVar End := date(2004,2,29);
local numbervar sats := DateDiff ("ww", Start, End, crSaturday);
local numbervar suns := DateDiff ("ww", Start, End, crSunday);

if dayofweek(start) = 7 then suns := suns + 1;
if dayofweek(start) = 1 then sats := sats + 1;

sats + suns
//end formula

Hope that helps,

ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.