英文:
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?
问题
以下是您要翻译的内容：
有条件：
来源 参数 开始工作 结束工作
聊天 周一至周五 8:00 - 21:00
电子邮件 周一至周五 9:00 - 18:00
聊天和电子邮件 周六和周日 9:00 - 14:00
全天休假（不工作）仅在公共假期，如1月1日、5月1日、12月25日 此处附有链接
Google表格公式用于计算收到工单和回复工单之间的时间戳差异的工作小时（以分钟为单位）是什么？
如果：
- 聊天工单于2022年12月31日星期六13:00:00收到
- 聊天工单于2023年1月2日星期一10:00:00回复
Google表格公式用于计算上述工单收到和回复之间的时间戳差异的工作小时（以分钟为单位）是什么？
预期答案是180分钟：我在星期六还有60分钟未回复，星期日跳过因为是公共假期，然后在星期一用了120分钟回复。我已经为其他情况做好准备，附在这个表格中 文本链接
感谢您的帮助！
英文:
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!
答案1
得分: 0
你可以尝试：
=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)))))),)))
英文:
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)))))),)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论