Hello,
I work in a team who delivers requests between 7h30 and 18h00, we agreed that we should deliver in a certain target, for instance a Top priority request should be delivered in 24 hours.
|
Request No |
Priority |
Incomedate |
Concludedvalidator |
PriorityHours |
|
AER-12334 |
Top |
13-07-2020 18:01 |
14-07-2020 16:30 |
24 |
|
AER-12335 |
Medium |
05-07-2020 16:16 |
12-07-2020 18:01 |
48 |
|
AER-12336 |
Top |
03-07-2020 07:00 |
10-07-2020 12:00 |
24 |
|
AER-12337 |
Urgent |
01-07-2020 12:15 |
04-07-2020 17:30 |
4 |
|
AER-12338 |
Low |
13-07-2020 06:00 |
14-07-2020 07:30 |
96 |
|
AER-12339 |
Medium |
08-07-2020 11:20 |
13-07-2020 07:31 |
48 |
|
AER-12340 |
Low |
11-07-2020 12:45 |
14-07-2020 17:00 |
96 |
But, for to calculate if the delivery time met the target, we can only count the time between the 07:30 and 18:00 and we should exclude Saturday and Sunday.
Firstly i used a DIFFDATE function between Concludedvalidator and Incomedate, counting the total hours elapsed.
Then i used a IF function to check if the request was treated on the same day it was received, and if not, subtracted 13,5 hours.
Then if there was a weekend day between the days i subtracted 24 hours for each day ( sunday and saturday ).
But the hours are not accurate.
Is there a simple way to calculate the time elapsed between two dates, considering working hours time frame and subtracting weekends ?
Thank you very much
Regards,
Rui Alves
|
Date |
Day |
Day of Week |
Day Name |
Holiday |
|
01-07-2020 |
1 |
2 |
quarta-feira |
Weekday |
|
02-07-2020 |
2 |
3 |
quinta-feira |
Weekday |
|
03-07-2020 |
3 |
4 |
sexta-feira |
Weekday |
|
04-07-2020 |
4 |
5 |
sábado |
Holiday |
|
05-07-2020 |
5 |
6 |
domingo |
Holiday |
|
06-07-2020 |
6 |
0 |
segunda-feira |
Weekday |
|
07-07-2020 |
7 |
1 |
terça-feira |
Weekday |
|
08-07-2020 |
8 |
2 |
quarta-feira |
Weekday |
|
09-07-2020 |
9 |
3 |
quinta-feira |
Weekday |
|
10-07-2020 |
10 |
4 |
sexta-feira |
Weekday |
|
11-07-2020 |
11 |
5 |
sábado |
Holiday |
|
12-07-2020 |
12 |
6 |
domingo |
Holiday |
|
13-07-2020 |
13 |
0 |
segunda-feira |
Weekday |
|
14-07-2020 |
14 |
1 |
terça-feira |
Weekday |
|
15-07-2020 |
15 |
2 |
quarta-feira |
Weekday |
