英文:
What is the Google sheets formula to calculate the working hours of timestamp difference for a specific working hours?
问题
以下是要翻译的内容：
在以下情况下：
- 工作时间从星期一至星期日，从09:00到18:00
- 只有在公共假期，如1月1日、5月1日、12月25日，才会全天休息（不工作）
A. 2023年4月30日星期日17:30接收的工单
B. 2023年5月2日星期二09:30回复的工单
Google表格中计算工单接收和回复之间时间戳差异的工作时间的公式是什么（以分钟为单位）？
预期答案是60分钟：我在星期日还剩下30分钟，但仍未回复，在星期一跳过，因为那是假日，然后在星期二用了30分钟来回复。
尝试使用NETWORKDAYS.INTL但没有成功。非常感谢您的帮助。
英文:
With the condition:
- The working hours from Monday to Sunday from 09:00 - 18:00
- 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.
答案1
得分: 0
Here is the translated content:
更新后的公式：
=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))))),)))
Please note that I've only provided the translated formula part as requested.
英文:
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))))),)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论