What is the Google sheets formula to calculate the working hours of timestamp difference for a specific working hours?

Question

With the condition:

1. The working hours from Monday to Sunday from 09:00 - 18:00
2. Full holiday (no working) only on public holiday such as January 1, May 1, December 25

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.

Docs link

Answer 1

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