Calculating the time duration between records excluding non working hours & days
Hi,
The scenario I have is workflow where applications pass through different stages, Each stage generates a new record in the dataset for that application. I want to calculate the time taken to progress between each stage. This I can calculate by subtracting the timestamp of the current record from the previous record, however my challenge is that I need to be able to factor out weekends, and non working hours and public holidays. During some parts of the year, the working days are only 6 hours compared to the usual 8 hours, so I need to be able to set the number of working hours on any given day in the year.
Lets Assume that the working week is 6 days Sunday to Thursday and the working hours are 8AM - 5PM. An application created at 4PM on Thursday and progressing to the next step on Sunday at 10AM should calculate a duration of 3 hours.
