This is possible. What you would do is set a date variable equal to the most recent date, then loop backwards to the oldest date decrementing the variable. Check the date against a list of holidays, perhaps in an external database, and against the day of the week. If it is not a weekend or a holiday, increment an int variable. At the end of the date loop the int variable will hold the number of non-holiday weekdays between the two dates.

-Ray

Hopefully, the difference between the two dates will be measured in days and not weeks.  Our goal is to complete the task in three working days or less.  I need to determine if the difference between the two dates includes weekend or holidays so I can delete them from my calculation.  So it seems that Ray's suggestion is probably best.

That being said, Ray, would it be possible for you to show me a quick example of how to do what you're suggesting?

I only tested this a little, but this should give you the number of weekdays between any two dates.  Its doesn't do the holidays, yet. You might also want to make sure end date is after start start, etc:

function WorkDays(start_date as date, end_date as date) as int
var days as int
var dd as date
var sub1 as int
var sub2 as int
var weeks as int
var the_dow as int
var remain as int

sub1 = 0
the_dow = @dow(start_date)
if(the_dow = 6)  // start is Saturday, advance two days
{
        sub1 = 2
}
else if(the_dow = 7) // start is Sunday, advance one day
{
        sub1 = 1
}
sub2 = 0
the_dow = @dow(end_date)
if(the_dow = 6) // end is Saturday, go back one day
{
        sub2 = 1
}
else if(the_dow = 7) // end is Sunday, go back two days
{
        sub2 = 2
}

// Calc total days
days = (end_date - sub2) - (start_date + sub1)

// Add one to make end date inclusive
days = days + 1

// Calc total weeks as an integer
weeks = days / 7

// Get the modulo in case it isn't an even number of weeks
remain = @mod(days, 7)

// Calc weekdays (5 in a week, plus any portion of a week left over)
days = (weeks * 5) + remain

return(days)
end function
 

Using hints from your example, I've been able to develop a for-next loop that checks the dates and "doesn't count" Sat and Sun.  My programming is below.

I'm now stuck trying to check a file of Holidays.  I've created a @xlu statement to see if the date is present in the holiday file.  Within the holiday file for each date there is an integer under the element VALID.  So, I expect the date to search in the Holiday file and for the vHolidayCheck variable to be 1 if there is a date.

Unfortunately, even when I know the date is present in the Holiday file, I'm not getting the 1 back.  Is there some trick to comparing dates from another file?  I've tried setting the date formats the same but that doesn't seem to work.

Please advise.



var vS as int
var vCount as int
var vdays as int
var vStartDate as date
var vCountDate as date
var vEndDate as date
var vHolidayCheck as int

vStartDate=OrderReceivedDate
vEndDate=BookDate

For vS = 0 to 20
     
     If vS=0
     {
           vCount=1
           VCountDate=vStartDate
     }
     else
     {
           vCountDate=vStartDate+vs
           
           If @dow(vCountDate)>5
           {
                 
                 vCount=vCount
           }
           Else
           {
                 vCount=vCount+1
           }
     }
     
// check if the date being checked is a holiday from the Holiday file
     
     vHolidayCheck = @XLU(@fn,vCountDate,"Holidays!Holidays","valid")
     
     writeln(vCountDate,vHolidayCheck)      

     if vHolidayCheck=1
     {
           vCount=vCount-1
     }


// check if the date being checked is the end date

     If vCountDate=vEndDate
     {
           
           vS=20
     }      

     TimeToEnter=vCount
Next