Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I have to calculate the time of taking charge of a ticket taking into account only the hours of opening and closing every day.
Example :
open-close
Monday : 08h00 - 12h00 and 14h00 - 16h00
Tuesday : 08h00 - 11h00 and 16h00 - 20h00
Wednesday : 11h00 - 16h00 and 21h00-Thursday08h00
Thursday : 10h00-15h00 and 20h00-Friday09h00
Saturday : 08h00-12h00
Here are the data
Date_recept Date_traitement
06/02/2017 07:00:00 06/02/2017 15:00:00
06/02/2017 21:00:00 07/02/2017 08:30:00
08/02/2017 22:00:00 09/02/2017 07:00:00
09/02/2017 16:00:00 09/02/2017 20:30:00
11/02/2017 11:00:00 13/02/2017 09:00:00
And I would like to have the following results
Date_recept Date_traitement Duration
06/02/2017 07:00:00 06/02/2017 15:00:00 05:00:00
06/02/2017 21:00:00 07/02/2017 08:30:00 00:30:00
08/02/2017 22:00:00 09/02/2017 07:00:00 09:00:00
09/02/2017 16:00:00 09/02/2017 20:30:00 00:30:00
11/02/2017 11:00:00 13/02/2017 09:00:00 02:00:00
Any ideas ?
Thanks in advance,
I agree with this... it might be possible to create a formula but i started down that rabbit hole and it gets a bit crazy, this is about 1/10th done if you want to take it from here
=if(floor(Date_traitement)=floor(Date_recept),
pick(
match(WeekDay(Date_recept),'Mon','Tue','Wed','Thu','Fri','Sat','Sun'),
(if(time(Date_recept)<'08:00:00' AND time(Date_traitement)>'12:00:00',4,if(time(Date_recept)>='08:00:00' and time(Date_recept)<'12:00:00',num(time(Date_recept) - '08:00:00'))))+//Monday am
(if(time(Date_traitement)>'16:00:00' and time(Date_recept)<'14:00:00',2,0)), //Monday pm
'Tue','Wed','Thu','Fri','Sat','Sun'),'x')
It's a time consuming work.. I can't do right now.. Will try later... Till then keep trying.!!!
sorry I am not the OP so won't be spending more time on this~!
Sorry for the confusion..
Hi
Will There be a case where say
Date_recept Date_traitement
06/02/2017 07:00:00 06/02/2017 11:00:00
or the time intervals specific to the each date?
first open-close is only applicable to Date_recept
Second open-close is only applicabel to Date_traitement
let me know please
Sasi
Hi Sasi
Date_recept = Date and time of receipt of ticket
Date_traitement = Ticket processing date and time
In fact, I want to count the waiting time of a ticket before its processing.
For your example there, the ticket is received on 06/02/2017 07:00:00 and processed on 06/02/2017 11:00:00 (24 hour format)
On Monday 06/02/2017, the opening time is at 08:00 and the closing at 12:00. Then for this ticket the waiting time is 3:00:00, It's counted from the opening time if the ticket is received outside the opening and closing time,
And the hours outside opening and closing hours are not taken into account.