在Excel中计算工作日和两天之间的时间 [英] Calculate Working days and time between two days in excel
问题描述
我已在excel公式下方编写了计算两个日期之间的工作时间* (不包括周六和周日)*.
I have written below excel formula to calculate working hours between two dates*(excluding sat and Sunday)*.
如果开始日期和结束日期在星期一至星期五之间，则效果很好.如果这两天都包括sat或Sunday，则将引发错误.
it works great if the start and end date is between Monday to Friday . if both days includes sat or Sunday then it raises error .
我正在寻求帮助，如果开始日期是星期六或星期日，则应将下一个工作日作为开始日；如果结束日期是星期六或星期日，则应将前一个工作日作为结束日.
I'm looking help if the start date is in Saturday or Sunday it should take next business day as a start day and if end date is in Saturday or Sunday it should take previous business day as a end day.
下面是示例和我的工作原理，然后应以h:mm格式返回结果
Startdate : enddate : Working hours(exc sat and sun)
9/28/2013 2:20 : 9/29/2013 2:39 : Both
9/29/2013 2:39 : 9/29/2013 2:39 : Both
9/29/2013 2:39 : 9/27/2013 13:18 : StartDate
9/27/2013 13:18 : 9/29/2013 2:22 : Enddate
10/1/2013 21:22 : 10/2/2013 0:33 : 3.10
9/30/2013 2:22 : 9/30/2013 5:22 : 3.00
和c2中的公式
=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7,WEEKDAY(B2)=1,WEEKDAY(B2)=7),IF(AND(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7)),"Both",IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"StartDate","Enddate")),TEXT(B2-A2-(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS(A2,B2)),"D")*24+TEXT(B2-A2-(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS(A2,B2)),"H.MM"))
让我知道您是否需要任何帮助
Let me know if you need any help
推荐答案
要计算两个日期之间的整个工作日(不包括这些日期):
To calculate whole working days between two dates, excluding those dates:
=MAX(NETWORKDAYS.INTL(A1+1,B1-1),0)
要计算第一个日期的工作时间(直到一天结束):
To calculate working hours (remaining till the end of the day) for the first date:
=IF(WEEKDAY(A1,2)<=5,ROUNDDOWN(A1+1,0)-A1,0)
要计算最后一个日期的工作时间(直到一天结束):
To calculate working hours (remaining till the end of the day) for the last date:
=IF(WEEKDAY(B1,2)<=5,ROUNDDOWN(B1+1,0)-B1,0)
总结这些，然后就可以了.
Sum these up and here you go.
这篇关于在Excel中计算工作日和两天之间的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
