0

How to account for holidays when calculating workdays?

Assume we are using the Project Template database.. A Project has a Beginning and an End function field that pulls the min / max of subproject dates.   I added a Holiday table that has a HolidayDate and a Description..   Basically .. I want to count the number of workdays between Beginning and End and subtract the number of Holidays that fall between the Beginning and End dates.. 

It should be something like.. 

workdays(Beginning, End) - cnt(select Holiday where HolidayDate >=Beginning and HolidayDate <=End )

However.. the cnt function yields an error of 

Field not found: Beginning at line 1 column 75

I get that the select may be confused as it is referencing the Holiday table..  In another thread / email I learned there is a "this" reference (though I cannot find anything in the manual)..  so while this.id returns the id.. this.Beginning .. also yields the same Field not found error.. 

So.. what actually works is... 
let b := Beginning;
let e := End;
workdays(Beginning, End) - cnt(select Holiday where HolidayDate >= b and HolidayDate <= e)

If you have another approach, I would be happy to see it. ;)

1 reply

null
    • Compleasy
    • silver_bee
    • 3 yrs ago
    • Reported - view

    Thanks for this. Works great!

Content aside

  • 3 yrs agoLast active
  • 1Replies
  • 1498Views