My application is about booking faults and then closing them when they have been resolved.
I need to calculate how long a fault has been open from the Time that it was assigned to a Team to the current Time or time it was closed.
Here's the tricky part:
1. The calculation should only calculate for the time period 08H00 to 16H00 of the week day, i.e. Monday to Friday. Example if I log a fault on Thursday at 15H30, the duration that it is open for is an hour at 08H00 on Friday morning.
2. The calculation should exclude weekends, i.e. Saturday and Sunday.
3. The calculation should exclude public holidays. (some where I have to define these days in order for my system to pick it up)
Any ideas as to how the problem will be solved?