Formula to get Date

[sajisher]

Hi
I am trying to get date excluding holidays and weekends.
Holiday stored in variable listOfMonthAndDays.

I have set system date December 02 , purposely given Nov 29 and 30 as holiday in the listOfMonthAndDays varibale.
UText15 = "Monthly" set monthly.

So it needs to give the final result in this case as Nov 28. But I get Nov 29.

Any one can foresee some error in this please. If there is one holiday , it works fine.

thanks

@OfDate
-------------
shared Datevar AsOfDate;
Stringvar listOfMonthAndDays;
listOfMonthAndDays := ";2010- 1| 1,;2010- 4| 2,;2010- 5|24,;2010- 7| 1,;2010- 8| 2,;2010- 9| 6,;2010-11|29,30,;2010-12|28,;"+ ";";
local stringvar tmp_del;
local numbervar y;
local numbervar m;
local numbervar d;
local numbervar d1;
local numbervar Flag;
local numbervar firstDay;
local stringvar tempStr;
local Datevar tempDate;
stringvar UText15;

UText15 := trim({FwdRate.UserText15});

If UText15 = "Monthly" or UText15 = "M" Then
(
If Year(currentdate) = 1.00 Then
AsofDate := DateSerial(Year(currentdate)-1, Month(currentdate ) , 1 - 1) //To handle Dec of last year when Jan next year
Else
AsofDate := DateSerial(Year(currentdate), Month(currentdate ) , 1 - 1)
)
Else If UText15 = "Weekly" or UText15 = "W" Then
(
AsofDate := DateSerial(Year(currentdate), Month(currentdate ) , Day(currentdate ) - 7);
If Dayofweek( AsofDate ) > 1 and Dayofweek( AsofDate ) < 6 then
AsofDate := AsofDate + ( 6 - Dayofweek( AsofDate ) )
)
Else If UText15 = "Daily" or UText15 = "D" Then
(
AsofDate := currentdate - 1
);

tempDate := AsofDate ;
y := Year(tempDate);
m := Month(tempDate);
d :=datepart("d",tempDate);
d1 :=datepart("d",tempDate);
Flag := 0;
////iTest := listOfMonthAndDays ;
while DayOfWeek(tempDate)= 1.00 Or Dayofweek(tempDate) = 7.00 or instr(listOfMonthAndDays,cstr(y,'####',0)+"-"+cstr(m,'##',0)+"|") > 0 and Flag = 0 Do
(
if DayOfWeek(tempDate)= 1.00 Or Dayofweek(tempDate) = 7.00 Then
(
tempDate := tempDate - 1;
Flag := 0 ;
);

if instr(listOfMonthAndDays,cstr(y,'####',0)+"-"+cstr(m,'##',0)+"|") > 0 Then
(
tempStr := mid(listOfMonthAndDays,instr(listOfMonthAndDays,cstr(y,'####',0)+"-"+cstr(m,'##',0)+"|")+length(cstr(y,'####',0)+"-"+cstr(m,'##',0)+"|"));
if instr(tempStr, ";")>0 then
tempStr := mid(tempStr,1,instr(tempStr, ";"))
else tempStr:="" ;
while instr(tempStr,",") > 0 Do
if ( instr(tempStr,",") > 0 ) Then
(
tmp_del := mid(tempStr,1,instr(tempStr,",") - 1 );
firstDay := cdbl( mid(tempStr,1,instr(tempStr,",") - 1 ) );
if firstday = d1 then
tempDate := datevalue(y,m,firstDay-1);

if length(tempStr) > instr(tempStr,",")+1 then
tempStr := mid(tempStr,instr(tempStr,",")+1)
else tempStr := "";
);
if DayOfWeek(tempDate)= 1.00 Or Dayofweek(tempDate) = 7.00 Then
(
tempDate := tempDate - 1;
Flag := 0
)
else ( Flag := 1 );
);


y := Year(tempDate);
m := Month(tempDate);
d :=datepart("d",tempDate);
d1 := d;

);

AsofDate := tempDate;
AsofDate

 

[Madawc]

There's an existing solution for finding working days, see faq767-4465. All you then need to do is to adjust this for holidays.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP