Hi, I know this is a FAQ but I can’t find much relating to my specific query. I have to calculate the target date of a task 10 working days from the date a complaint started. I need to include English bank holidays in this.
So for instance, if a complaint is started on 9/4/2014, I need it to return a target date of 10 working days after the 9/4/2014.
There’re 2 bank holidays (18/04/2014 & 21/04/2014) so the target date to be returned is 24/4/2014.
I’ve been using relativedate(startdate;14) but I need to factor in where there may be additional holidays to add to the target.
Thanks. I’d read both of those. I’m trying to calculate the end date rather than the working days between - which is always 10. The system that we’re using allows the users to change the target dates (which is appropriate in some circumstances) but it means that the target dates in the database aren’t always reliable for reporting.
I’ll start by creating a calendar table of non-working days.
No worries - it all helped. Sorted it. I’ve created a derived table of non-working days, then in webi, counted the number of non-working days between the start date and the start date plus 14. Then added 10 plus the count of non-working days to the start date to get the actual target date:
=Count([Non Working Day]Where([Non Working Day]Between([Reported Date];RelativeDate([Reported Date];14))))
Sounds like you have an answer you’re happy with but let me offer that I recently had a report with a work days vs calendar day comparison report, and found it was unreasonable to build in Webi, and Crystal’s Dateadd(“w”) function easily accounted for holidays/weekends.
Weekends maybe. Its usually bank holidays that cuase issues as these are not the same each year.
I usually recommend a DB function for this, unless you incorporate them into your calendar table. Either way you have to maintain this data each and every year.
Unfortunately my initial solution didn’t workout for all cases. I’ve end up building a date table and a derived table in the universe with a nasty bit of SQL
(BOB member since 2012-08-16)
.
(BOB member since 2005-01-06)