使用日期中的x个“工作日”计算日期。 [英] Calculate a date using x number of 'working days' from a date.
问题描述
我有一个指定的SHIP DATE。我需要计算在发货日期前9个工作日的预期DUE日期，以及在SHIP DATE之前16个工作日的START DATE。工作日是通过FRI的MON。我并不担心在这一点上取消假期，但如果不是太复杂，那么在某些时候这样做会很好。我看到很多关于如何计算两个日期之间的工作日的回复，但没有关于如何根据日期之前的x个工作日来计算日期。
I have an assigned SHIP DATE. I need to calculate an expected DUE date which is 9 working days prior to the ship date, and a START DATE which is 16 working days prior to the SHIP DATE. Working days are MON thru FRI. I am not concerned with eliminating holidays at this point but it would be nice, if not too complicated to do that at some point. I see many responses on how to calculate working days between two dates, but nothing on how to calculate a date based on x number of working days prior to a date.
推荐答案
我真的没有看到任何简单的方法。
需要的逻辑：
M - F，5天
所以一个人会需要确定两天之间是否超过5天，然后确定是否有非工作日，并考虑到那些...
今天需要2015-06-23
回去9天...所以如果我们把5比9拿到，我们得到1.8，或7天或一周...我们需要在一周内完成工作日，所以5 * 1周...给我们5天.... 9减5我们4天离开...
所以采取2015-06-23，减去7天给我们2015-06-16 ...现在循环一天（15日）星期一（4-1 = 3），循环回来，星期天......我们可以跳过那一天，下一个星期二让我们在下一个星期五12日（3-1 = 2）循环接下来的两天假我们的开始日期为2015-06-10
现在我们已经制定了逻辑（这花了我一些时间来计算）我们可以编写我们的函数
注意这是空气代码...意思是我没有在VBA-Editor中检查过这个。我遵循上面的逻辑并按照我的方式构建...函数将在查询中调用或通过VBA等调用..
>>一个注意：我通常不会提前提供代码;但是，我可以看到这对许多人来说具有很大的价值......我也许也可以使用它。
FindDueDate（[Date_value_or_field]， days_to_back_calc）
...
FindDueDate（＃06/23/2015＃，9）
I really do not see any easy method here.
The logic one would need:
M - F, 5 days
So one would need to determine if more than 5 days between the days then determine if there are non-weekdays and account for those...
So take today 2015-06-23
Go back 9 days... so if we take 5 into 9 we get 1.8, or 7 days or one week... we need to pull off the workdays out of the week, so 5 * 1 week... gives us 5 days.... 9 less the 5 leaves us 4 days to step thru...
So take 2015-06-23, subtract the 7 days to give us 2015-06-16... now loop backwards a day (15th) Monday (4-1 = 3), loop back, Sunday... we can skip that day and the next so sub 2 gives us the next Friday the 12th (3-1 =2) loop the next two days out leaves us with a start day of 2015-06-10
So now that we have the logic worked out (and that took me awhile to figure) we can write our function
Caution this is air code... meaning I have not checked this in the VBA-Editor. I''m following the logic above and building as I go... The function would be called in the Query or via VBA etc as..
>>One Note: I don''t usually provide the code upfront; however, I can see where this would have a great deal of value for many people... and I might be able to use it too.
FindDueDate([Date_value_or_field],days_to_back_calc)
...
FindDueDate(#06/23/2015#,9)
这是一个很酷的功能Zmbd。我一直在使用Microsoft提供的这个： https：//支持.microsoft.com / zh-CN / kb / 115489
That''s a cool function Zmbd. I''ve been using this one provided Microsoft: https://support.microsoft.com/en-us/kb/115489
车轮已经是的数字发明了这就是我睡觉前检查的东西！
（rotfl）
Figures that the wheel would already be invented that''s what I get for checking things before bed!
( rotfl )
这篇关于使用日期中的x个“工作日”计算日期。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
