Hi,
I have to calculate the duration of incidents taking into account working, public holidays and time slots.
Monday to Friday: open from 7:30 a.m. to 6:30 p.m.
Saturday: open from 7:30 a.m. to 5:30 p.m.
In my data table, I have my Incident Number, Start Date, Start Time, End Date, End Time columns
And I also have a "Holidays" table which lists the holidays of the year
If I have an incident open on Saturday 04/16 at 8:00 a.m. and closed on Wednesday 04/20 at 5:30 p.m., knowing that Monday 04/18 was a public holiday.
Its duration would be :
For Saturday: 5h30 p.m. - 8h00 a.m. = 9h30
For Sunday: 0 because non-working day
For Monday: 0 because public holiday
For Tuesday: 6h30 p.m. - 7h30 a.m. = 11h
For Wednesday: 5h30 p.m. - 7h30 a.m. = 10h
That is a total duration of 30h30
How can I create the measure to achieve my goals?
Thanks for your help
