google-sheetstimestampgoogle-sheets-formuladate-differenceticket-system

What is the Google Sheets formula to calculate the working hours for each specific day based on the source of the chat and the given work schedule?

With the condition:

Source Parameters Start Work End Work Chat Monday - Friday 8:00 - 21:00 Email Monday - Friday 9:00 - 18:00 Chat and Email Saturday and Sunday 9:00 - 14:00

Full holiday (no working) only on public holiday such as January 1, May 1, December 25 as attached here

What is the Google sheets formula to calculate the working hours of timestamp difference between ticket received and ticket responded above (in minutes)?

IF:

What is the Google sheets formula to calculate the working hours of timestamp difference between ticket received and ticket responded above (in minutes)?

The expected answer is 180 minutes: I have 60 minutes left on Saturday but still not responded, skip on Sunday because it is public holiday, and used 120 minutes on Mondday to reply it. I already prepared for others scenario as attached in this sheettext

Thanks for your help!

Solution

  • You may try:

    =map(E7:E,F7:F,G7:G,lambda(source,created,response,if(len(source)*len(created)*len(response)*(response>=created),let(Σ,sequence(days(response,created)+1,1,created,1),
           Λ,ifna(filter(Σ,iserror(xmatch(Σ,tocol(Holidays!A2:A,1))))),s_,xlookup(source,B2:B3,C2:C3),e_,xlookup(source,B2:B3,D2:D3),
           sum(map(Λ,lambda(Δ,let(start_,if(weekday(Δ,2)<6,s_,C4),end_,if(weekday(Δ,2)<6,e_,D4), round(ifs(
                     min(Λ)=max(Λ),  max(min(Λ+end_,response),Λ+start_)-min(max(Λ+start_,created),Λ+end_),
                     min(Λ)=Δ,       ifs((Δ=int(created))*(timevalue(created)<=end_),end_-max(start_,timevalue(created)),Δ<>int(created),end_-start_,1,),
                     max(Λ)=Δ,       ifs((Δ=int(response))*(timevalue(response)>=start_),min(end_,timevalue(response))-start_,Δ<>int(response),end_-start_,1,),
                     1,              end_-start_)
                     *24*60)))))),)))

    enter image description here