With the condition:
A. Ticket received on Sunday, April 30, 2023 at 17:30 B. Ticket responded on Tuesday, May 2, 2023 at 09:30
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 60 minutes: I have 30 minutes left on Sunday but still not responded, skip on Monday because it is holiday, and used 30 minutes on Tuesday to reply it.
Tried to use NETWORKDAYS.INTL but it does not work. Really appreciate with your help.
Updated formula:
=map(F4:F,G4:G,lambda(created,response,if(len(created)*len(response)*(response>=created),let(Σ,sequence(days(response,created)+1,1,created,1),
Λ,ifna(filter(Σ,iserror(xmatch(Σ,tocol(Holidays!A2:A,1))))),
sum(map(Λ,lambda(Δ,round(ifs(
min(Λ)=max(Λ), max(min(Λ+C1,response),Λ+B1)-min(max(Λ+B1,created),Λ+C1),
min(Λ)=Δ, ifs((Δ=int(created))*(timevalue(created)<=C1),C1-max(B1,timevalue(created)),Δ<>int(created),"09:00:00",1,),
max(Λ)=Δ, ifs((Δ=int(response))*(timevalue(response)>=B1),min(C1,timevalue(response))-B1,Δ<>int(response),"09:00:00",1,),
1, "09:00:00")
*24*60))))),)))