Time between tow timestamps

[harson]

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,

[adamdavi3s]

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')

[MK_QSL]

It's a time consuming work.. I can't do right now.. Will try later... Till then keep trying.!!!

[adamdavi3s]

sorry I am not the OP so won't be spending more time on this~!

[MK_QSL]

Sorry for the confusion..

[sasiparupudi1]

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

[harson]

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.