# Calculating working hours without weekends

• ### Question

• 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

Wednesday, July 15, 2020 12:38 AM