如果时间戳记发生在工作时间之外，如何将日期时间戳记提前到下一个工作日开始? [英] How do I advance a date timestamp forward to the beginning of the next work day if the timestamp occurs outside of working hours?
问题描述
我有一个日期时间戳列表，如果任何时间发生在我们的工作时间之外，我需要将它们提前到下一个工作日的开始. 我们的工作时间为M-F 8:30 am-5:30pm，星期六8:30 am-1:30pm，星期日关闭，节假日也关闭. 例子:
I have a list of date timestamps and if any of the times happen outside of our working hours, I need to advance them forward to the start of the next workday. Our work hours are M-F 8:30am-5:30pm, Saturday 8:30am-1:30pm, Sunday closed and we are also closed on holidays. Examples:
- 2020年6月26日星期五6:30 pm应该提前到2020年6月27日星期六8:30 am
- 应将2020年7月3日(星期五)下午6:30提前至2020年7月6日(星期一)上午8:30.因为星期六是假期，我们星期天不营业.
- 不应提前至2020年7月3日(星期五)下午5:29，因为它是在工作时间开始的
Column A |Column B | Column C | Column B | Column E
Underwriter | LoanNumber | EntryTime_+3hrs | Desired Outcome | HOLIDAY
TOM | 1 | 07/31/2020 8:28:42 AM | 08/01/2020 8:30:00 AM | 01/01/2020
DICK | 2 | 07/30/2020 6:32:36 PM | 07/31/2020 8:30:00 AM | 01/20/2020
JANE | 3 | 07/30/2020 4:18:57 PM | 07/30/2020 4:18:57 PM | 02/17/2020
BETH | 4 | 07/30/2020 3:06:18 AM | 07/30/2020 8:30:00 AM | 05/25/2020
SALLY | 5 | 07/29/2020 6:35:37 PM | 07/30/2020 8:30:00 AM | 07/04/2020
GEORGE | 6 | 07/03/2020 7:45:26 PM | 07/06/2020 8:30:00 AM | 09/07/2020
| | | | 10/12/2020
| | | | 11/11/2020
| | | | 11/26/2020
| | | | 12/24/2020
| | | | 12/25/2020
推荐答案
开始日期时间戳需要验证并转换为下一个可用时隙.
Start date time stamps need to be validate and converted to next available time slot.
当前的想法是您有三个潜在的结果:
Current thinking is you have three potential outcomes:
- 日期可能需要从0830开始转移到下一天
- 日期将保持不变，但时间将移至0830
- 日期和时间有效，无需调整.
所以通用公式可能看起来像这样:
so a generic formula might look something like this:
IF(OR(HOLIDAY,SUNDAY,AFTERHOURS),FIND NEXT WORKDAY,
IF(BEFORE WORKHOURS, SET TIME TO START TIME, ITS VALID TIME))
为了单独检查每个条件是否有可能的日间班次，我们可以使用以下公式:
In order to check each condition individually for a potential day shift we can use the following formulas:
(ASSUME C2 is the start date being tested)
WEEKDAY AFTER 1730
=AND(MOD(C2,1)>TIME(17,30,0),OR(WEEKDAY(C2)={2,3,4,5,6}))
SATURDAY AFTER 1330
=AND(MOD(C2,1)>TIME(13,30,0),WEEKDAY(C2)=7)
HOLIDAY (list in E2:E12)
=COUNTIF($E$2:$E$12,INT(C2))=1
SUNDAY
=WEEKDAY(C2)=1
前两个公式中的MOD函数将去除Integer/date值，而仅保留小数点/时间部分
The MOD function in the first two formulas is stripping the Integer/date value and just keeping the decimal/time portion
{}是一个手动列表/数组，是执行多个OR检查的好方法，而无需写出每个检查.由于这些是顺序的，因此您还有其他选择.
The { } is a manual list/array is a nice way of doing multiple OR checks with out writing out each individual check. Since these are sequential, you do have other options.
现在有一种标记每种情况的方法，所以现在只需重新排列和分组在一起，以便在嵌套的IF函数中有三个选择:
Now there is a way of flagging each case, So now just rearrange and group together so you have three choices in a nested IF function:
=IF(OR(AND(MOD(C2,1)>TIME(17,30,0),OR(WEEKDAY(C2)={2,3,4,5,6})),AND(MOD(C2,1)>TIME(13,30,0),WEEKDAY(C2)=7), COUNTIF($E$2:$E$12,INT(C2))=1, WEEKDAY(C2)=1), FIND NEXT DATE, IF(BEFORE WORK, SET TIME TO 0830, DO NOTHING))
由于每个有效日均始于0830，并且第一个IF已经处理了无效日，因此仅需要检查开始时间.
Since each valid day starts at 0830 and invalid days have already been taken care of with the first IF, only the start time needs to be checked.
=MOD(C2,1)<TIME(08,30,00)
有效时间是唯一剩下的情况，因此没有要检查的东西.
And the valid time is the only case left over so there is nothing to check for.
您的伪公式变为:
=IF(OR(AND(MOD(C2,1)>TIME(17,30,0),OR(WEEKDAY(C2)={2,3,4,5,6})),AND(MOD(C2,1)>TIME(13,30,0),WEEKDAY(C2)=7), COUNTIF($E$2:$E$12,INT(C2))=1, WEEKDAY(C2)=1), FIND NEXT DATE, IF(MOD(C2,1)<TIME(08,30,00), SET TIME TO 0830, DO NOTHING))
因此，现在您只需要弄清楚如何增加几天，更改时间并保持所拥有的价值即可.
So now you just need to figure out how to add some days, change the time, and keep what you have for a value.
调整添加的天数使我查看了多个嵌套的IF，它等于连续无效的天数.现在，我有一个最坏的情况，那就是星期四17:31.星期五是假日，是星期六假日的结果，星期天是假日，星期一是由于星期日假日而定的假日.因此，可能的第一天将是5天.嵌套的IF丑陋.作为替代方案，我查看了AGGREGATE，每次最多添加1天，最多5天，并检查其是否为有效日期.然后使用最低/最早的日期，并将开始时间设置为0830.为此，我尝试了以下公式:
Adjusting the number of days to add had me looking at multiple nested IF equal to the number of days in a row that could be invalid. Right now I have the worst case scenario being Thursday at 17:31 something starts. Friday is a holiday as a result of a holiday on Saturday, Sunday is a holiday, and Monday is a holiday due to the Sunday holiday. So first potential day would be 5 days away. Ugly nested IF. As an alternative I looked at AGGREGATE and adding 1 day at a time up to 5 and checking if its a valid date. Then take the lowest/earliest date and setting start time to 0830. To achieve this I tried the following formula:
=AGGREGATE(15,6,(INT(C2)+{1,2,3,4,5})/((COUNTIF($E$2:$E$12,(INT(C2)+{1,2,3,4,5}))<1)*(WEEKDAY(C2+{1,2,3,4,5})<>1)),1)+TIME(8,30,0)
然后您需要做的下一个功能是保留日期，但将时间设置为08:30
Then next function you need to do is keep the date but set the time to 08:30
=INT(C2)+TIME(08,30,00)
您什么都不做:
=C2
所以现在，如果我们将废话结合到一个公式中，我们将得出:
so now if we combine the crap out of that into one formula we wind up with:
=IF(OR(AND(MOD(C2,1)>TIME(17,30,0),OR(WEEKDAY(C2)={2,3,4,5,6})),AND(MOD(C2,1)>TIME(13,30,0),WEEKDAY(C2)=7), COUNTIF($E$2:$E$12,INT(C2))=1, WEEKDAY(C2)=1), AGGREGATE(15,6,(INT(C2)+{1,2,3,4,5})/((COUNTIF($E$2:$E$12,(INT(C2)+{1,2,3,4,5}))<1)*(WEEKDAY(C2+{1,2,3,4,5})<>1)),1)+TIME(8,30,0), IF(MOD(C2,1)<TIME(08,30,00), INT(C2)+TIME(08,30,00), C2))
我认为D2(红色背景)中的期望日期是错误的，应该改为20/07/31 08:30
I believe the desired date in D2 (red background) is wrong and should instead be 20/07/31 08:30
从理论上讲，现在可以用第一个公式中的每个B2引用代替它，但是A)事情变得比原来更加难以理解和难以维护，并且B)可能导致多次重复计算.
Now in theory this could be substituted for every B2 reference in the first formula, but A) the thing would become even more damned unreadable and hard to maintain than it already is, and B) may cause multiple repetitive calculations.
这篇关于如果时间戳记发生在工作时间之外，如何将日期时间戳记提前到下一个工作日开始?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
