Excel Forumula-添加日期和时间的工作时间 [英] Excel Forumula - Adding working hours to date and time

问题描述

我目前正在使用论坛来将工作时间添加到开始日期和时间:

I am currently using a forumla to add working hours to a start date and time:

=WORKDAY(A1,INT(B1/8.5)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),1,0),$D$1:$D$21)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),TIME(9,0,0)+TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)-TIME(17,30,0),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0))

A1单元格是开始日期，B1单元格是要添加的小时数，D列是银行假期

Cell A1 is the start date, Cell B1 is the amount of hours to add, and column D is bank holidays

到目前为止，这很好用，但是，如果开始日期和时间在9:00到17:30之间，则显示错误的日期和时间.

So far this works great, however if the start date and time is outside of the 9:00 to 17:30, then it shows the wrong date and time.

有什么原因会发生这种情况吗?我该怎么做才能克服这个问题?

Is there any reason why this happens? and what i can do to overcome this issue?

例如:

        A               B             C                   D
24/08/2019 10:31:17    16      28/08/2019 09:31       26/08/2019   

C列是论坛所在的位置，在此示例中，仅添加9个小时.列A的日期在星期六的工作时间之外记录.正确的日期和时间应为28/08/2019 16:30，因为它将在星期一的银行假日后开始计算.

Column C is where the forumla sits and in this example of only adding 9 Hours. Column A's date is logged outside of working hours, on a saturday. The correct date and time should be 28/08/2019 16:30 as it will start counting after bank holiday on the Monday.

推荐答案

我自己使用了两组公式来解决此问题，具体取决于日期和时间是否在工作时间之内:

I resolved the issue myself using two sets of formulas depending if the date and time falls in or out of working time:

=IF(AND(WEEKDAY(A1,2)<6,HOUR(A1)>9,HOUR(A1)<17.5,COUNTIF(D1:D21,DATE(YEAR(A1),MONTH(A1),DAY(A1)))=0),WORKDAY(A1,INT(B1/8.5)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),1,0),$D$1:$D$21)+IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),TIME(9,0,0)+TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)-TIME(17,30,0),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)),WORKDAY(WORKDAY(A1,1,D1:D21)+TIME(9,0,0),INT(B1/8.5)+IF(TIME(HOUR(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),MINUTE(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),SECOND(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),1,0),$D$1:$D$21)+IF(TIME(HOUR(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),MINUTE(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),SECOND(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)>TIME(17,30,0),TIME(9,0,0)+TIME(HOUR(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),MINUTE(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),SECOND(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)-TIME(17,30,0),TIME(HOUR(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),MINUTE(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)),SECOND(WORKDAY(A1,1,D1:D21)+TIME(9,0,0)))+TIME(MOD(B1,8.5),MOD(MOD(B1,8.5),1)*60,0)))

这篇关于Excel Forumula-添加日期和时间的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！