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?

Question

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:

• Ticket from CHAT received on Saturday, 12/31/2022 13:00:00
• Ticket from CHAT responded on Monday, 1/2/2023 10:00:00

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!

Answer 1

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