Calculate final date work days
How can we calculate a final date only on working days (without weekends and holidays)?
today()+Duration
I tried to do a loop and check if it's weekend or vacation (with vacation table for p in select vacation_table), but the calculation is slow.
5 replies
-
This is the last working day of the current week:
–––
var myDay := today();
let myWeek := week(today());
while week(myDay) = myWeek do
myDay := myDay + 1
end;
myDay - 3
–––Birger
-
Or this: date(year(today()), month(today()), day(today()) - weekday(today()) + 4)
-
weekday(Date) will be 0 for Monday to 6 for Sunday
-
I like to say that I often walk around the block to go next door... There may be an easier way.. but this will calculate an end date given a start date and a DURATION in workdays .. it ignores holidays.. If you want to make it EFFORT based.. See the Use Case about calculating workdays accounting for holidays..
let d := 7 - weekday('Start Date');
let t := DURATION - d;
let w := floor(t / 5);
let r := t - 5 * w;
let o := d + w * 7 + r + 1;
date(year('Start Date'), month('Start Date'), day('Start Date') + o) -
Well.. I guess if it was
let d := 6 - weekday('Start Date') ..
I would not have to add 1 back at the end.. :)
Content aside
- 6 yrs agoLast active
- 5Replies
- 2644Views