My code for calculate exclusion of weekend & holiday is not working?! 1

[tincan56]

I have tried everything and still not working. I am really stuck. Can anyone help me please? Thank you so much!

I have a report right now that returns all data that counts weekend and holiday as a weekday. So therefore, I am adding the code (below) that maturity minus(-) settlement date does not fall on weekend and holiday. However when I run the report, I am still getting all data.

I created the code below in Formula Editor called ExWendsHoli. And in Select Expert, I added ExWendsHoli and
set it not equal to 1 (@ExWendsHoli}<>1. So, if I my settle date is 7/3/02 and maturity date is 7/5/02. And since 7/4/02 is a holiday I dont' want to see all records with 1 day of working day (maturity - settle) in the report.
****************************************************
DateVar Array PubHols:=[Date(2002,07,04)];

NumberVar StartDay:= DayOfWeek({transrpt.settle_dt});
NumberVar ElapsedDays:={transrpt.mature_dt}-{transrpt.settle_dt};
NumberVar WorkingDays:=0;
NumberVar NoPubHols:=Count(PubHols);
NumberVar i:=0;

NumberVar FirstWeek:= 7 - StartDay;
If ElapsedDays > FirstWeek Then
WorkingDays:=ElapsedDays-((1+(Truncate((ElapsedDays-(FirstWeek+2))/7)))*2)
else
WorkingDays:=ElapsedDays;

For i:=1 to NoPubHols Do
if PubHols in {transrpt.settle_dt} to {transrpt.mature_dt} then
Workingdays:= WorkingDays - 1;

Workingdays

 

[RobertKaiserIII]

I worked out a formula to do this some time ago, here's what I got (you'll need to make hopefully minor modifications to adapt it for your use).

Assuming:
?FirstDate = Start date
?SecondDate = End Date

NumberVar Idx := 1;
NumberVar BusinessDays := 0;

// This array will store all the holidays
// Add additional holidays to the list in YYYY,MM,DD format
DateVar Array Holidays := [date(2001,01,01),date(2001,02,19),
date(2001,05,28), date(2001,06,04)];

// This code will eliminate the WEEKENDS from the date range
BusinessDays := datediff (&quot;d&quot;, {?FirstDate}, {?SecondDate}) -
datediff (&quot;ww&quot;, {?FirstDate}, {?SecondDate}, crSaturday) -
datediff (&quot;ww&quot;, {?FirstDate}, {?SecondDate}, crSunday);

// This code will eliminate HOLIDAYS that fall on weekdays from the number of business days
for Idx:= 1 to ubound(Holidays) do
(
if dayofweek(Holidays[Idx]) <> 1 or dayofweek(Holidays[Idx]) <> 7 then
(
if Holidays[Idx] in {?FirstDate} to {?SecondDate} then
BusinessDays := BusinessDays - 1;
);
);
BusinessDays;

 

[kenhamady]

I have a formula to do this in faq149-243. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[tincan56]

Thank you - RobertKaiserIII (Programmer) !!! Your code worked like a charm!

I guess the code that I have somehow has a mysterious bug in it.

: )

 

[synapsevampire]

This sort of functionality, as well as trying to provide information in CR for dates that aren't in the return set are easily handled by creating a Periods (dates) table in your database.

Here's a create script for SQL Server, then just populate it with all dates and then you can always join to it for this type of information. This particular one is to be used for my data warehouse, so you might shrink it down a bit.

CREATE TABLE [dbo].[AtsDWPeriod] (
[PERIODID] [int] IDENTITY (1, 1) NOT NULL ,
[dtsActivityDate] [smalldatetime] NULL ,
[intYear] [int] NULL ,
[intQuarter] [int] NULL ,
[intMonthNum] [int] NULL ,
[chrMonthName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrMonthAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intWeekNum] [int] NULL ,
[intDayNum] [int] NULL ,
[chrDayName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrDayAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intFiscalYear] [int] NULL ,
[intFiscalQuarter] [int] NULL ,
[intFiscalMonthNum] [int] NULL ,
[intFiscalWeekNum] [int] NULL ,
[bitAcctCycleEnd] [bit] NULL ,
[bitPublicHoliday] [bit] NULL ,
[bitNonWorkingDay] [bit] NULL ,
[bitSpecialWorkDay1] [bit] NULL ,
[bitSpecialWorkDay2] [bit] NULL ,
[dtmCreated] [datetime] NULL ,
[chvCreator] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

I also have a script to populate it if you're interested.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[tincan56]

What if I have to INCLUDE the holidays or weekends but exclude the weekdays?!

Does anyone have sample code that I can reference to? Thank you so much....

 

[RobertKaiserIII]

I don't have time to figure out a separate formula, so I'll give you a quick and dirty method that will basically just invert the other formula.

Assuming:
?FirstDate = Start date
?SecondDate = End Date

NumberVar Idx := 1;
NumberVar BusinessDays := 0;
NumberVar TotalDays := 0; // <---- NEW

// Get the total number of days__________________

TotalDays := datediff (&quot;d&quot;, {?FirstDate}, {?SecondDate})

// Get the total number of business days_________

// This array will store all the holidays
// Add additional holidays to the list in YYYY,MM,DD format
DateVar Array Holidays := [date(2001,01,01),date(2001,02,19),
date(2001,05,28), date(2001,06,04)];

// This code will eliminate the WEEKENDS from the date range
BusinessDays := datediff (&quot;d&quot;, {?FirstDate}, {?SecondDate}) -
datediff (&quot;ww&quot;, {?FirstDate}, {?SecondDate}, crSaturday) -
datediff (&quot;ww&quot;, {?FirstDate}, {?SecondDate}, crSunday);

// This code will eliminate HOLIDAYS that fall on weekdays from the number of business days
for Idx:= 1 to ubound(Holidays) do
(
if dayofweek(Holidays[Idx]) <> 1 or dayofweek(Holidays[Idx]) <> 7 then
(
if Holidays[Idx] in {?FirstDate} to {?SecondDate} then
BusinessDays := BusinessDays - 1;
);
);

// Subtract the total number of business days from the total number of days to get the number of weekends and holidays_________


TotalDays := TotalDays - BusinessDays;

 

[RobertKaiserIII]

I forgot to add the semicolon after the line below.

// Get the total number of days__________________

TotalDays := datediff (&quot;d&quot;, {?FirstDate}, {?SecondDate});

 

[tincan56]

Mr. KaiserIII,

I am confused now...
I DON'T WANT the data to show, only if the difference between
Start and End date is equal to 1. (an overnite weekday trade). if it falls on the weekend, we will include the weekend as weekdays, and still count each day as a weekday.
and same for the holiday.
so i don't really need to eliminate weekends or holidays.

i commented all the code except to find the date difference between the first and second date (datediff), but i am not getting what i want in the report (i am still seeing the overnite data showing).

Question, in the select expert, it is filtering out the data that you only want correct? (i tried to set the forumla <> 1 cuz i don't want to see the data)

i will keep trying, meanwhile, can you give me some suggestions? thank you very much.

 

[RobertKaiserIII]

Hmm. I got a little confused myself so I carefully re-read everything you've written, and I think I understand. Please correct me if I'm mistaken.

You want to FILTER OUT any record where there is a one WORKING day difference between the Settle date and Maturity date.

What happens if the maturity date falls on a holiday or weekend and the settle date is on a weekday? Say, 7/3/02 is a weekday, and 7/4 and 7/5 are both holidays, do you exclude that record? If so, here's something you could try (mind you that I haven't tested this or anything):

NumberVar Idx := 1;
NumberVar BusinessDays := 0;
BooleanVar EvaluateFormula := False;

// This array will store all the holidays
// Add additional holidays to the list in YYYY,MM,DD format
DateVar Array Holidays := [date(2001,01,01), date(2001,02,19), date(2001,05,28), date(2001,06,04)];

// Only evaluate this formula if the mature date falls on a non-holiday weekday
if (DayOfWeek({transrpt.mature_dt}) <> crSaturday and DayOfWeek({transrpt.mature_dt}) <> crSunday) then
(
if not({transrpt.mature_dt} in Holidays) then
EvaluateFormula := True;
);

if EvaluateFormula = True then
(
// This code will eliminate the WEEKENDS from the date range
BusinessDays := datediff (&quot;d&quot;, {transrpt.settle_dt}, {transrpt.mature_dt}) -
datediff (&quot;ww&quot;, ({transrpt.settle_dt}, {transrpt.mature_dt}, crSaturday) -
datediff (&quot;ww&quot;, {transrpt.settle_dt}, {transrpt.mature_dt}, crSunday);

// This code will eliminate HOLIDAYS that fall on weekdays from the number of business days
for Idx:= 1 to ubound(Holidays) do
(
if dayofweek(Holidays[Idx]) <> 1 or dayofweek(Holidays[Idx]) <> 7 then
(
if Holidays[Idx] in {transrpt.settle_dt} to {transrpt.mature_dt} then
BusinessDays := BusinessDays - 1;
);
);

BusinessDays;
);

 

[RobertKaiserIII]

tincan56,

I hope my last reply will help you. Unfortunately (/fortunately) I won't be around to check for replies as I am leaving for Las Vegas for the weekend in a few hours.

Best of luck!

Robert

 

[tincan56]

Mr.Kaiser,

Right now, the system is excluding holidays and weekends,
however I want to reverse that, which counts holidays and weekends as regular week day.

The code for regular holiday week day is working for me right now, but
I am still trying to figure out the part that will reverse
holidays and weekends into a regular week day.

Thank you very much for your help again.