确定日期是否为Excel中的工作日 [英] Determine if date is a workday in Excel

问题描述

我在H10列中有一个日期，并且需要在下一个I列中为此日期添加45天

1. 如果没有日期，列I必须为空白
2. 如果第45天是周末，则计算必须移至下一个工作日，即星期一

解决方案

您需要结合两个基本功能.

首先， DATE + INT = DATE .例如，如果 H10 = 1/8/2015 和 H11 = H10 + 10 ，则H11将显示 1/18/2015 .

您要使用 H10 + 45 .

第二，您可以使用 Weekday(date，mode)函数确定星期几.就您个人而言，您可以使用 weekday(h10 + 45，2)，对于MTWRF，该数字为1-5，在周末为6-7.所以像

  = if(工作日(h10 + 45,2)< 6，工作日"，周末")= if(weekday(h10 + 45,2)= 1，"Monday !!"，"not monday ...") 

但是我们还没有完成-您需要确保您的一天实际上在工作日结束.因此，我们可以执行类似的操作-在确定工作日时，可以使用它来确定需要添加的数量.如果我们以6(星期六)结束，我们想增加2天以将其推迟到星期一.对于7，我们想增加1天以将其推迟到星期一.因此，我们可以简单地将 8-工作日(h10 + 45)添加为工作日.所以我们的增值变成了

 //确定星期几类型，星期几，周末，所以加上偏移量= if(工作日(h10 + 45)< 5，h10 + 45，h10 + 45 +(8-工作日(h10 + 45)) 

您还需要保留空白，因此您需要包装使用的所有内容

  = if(isblank(h10)，"，/*此处为实函数*/) 

I have a date in column H10 and need to add 45 days to this date in the next Column I

1. If there are not dates Column I must be blank
2. If the 45th day falls on a weekend the calculation must move to the next workday which is Monday

解决方案

You need to combine two fundamental functions.

First, DATE + INT = DATE. For example, if H10 = 1/8/2015 and H11 = H10 + 10 then H11 will show 1/18/2015.

In your case, you want to use H10 + 45.

Second, you can use the Weekday(date,mode) function to determine the day of the week. Personally, for your purpose, you could use weekday(h10 + 45, 2) which would give a 1-5 for MTWRF, and a 6-7 for a weekend day. So something like

=if(weekday(h10+45,2) < 6, "weekday", "weekend")

=if(weekday(h10+45,2) = 1, "Monday!!", "not monday...")

But we aren't done yet - you need to make sure your day actually ends up on a weekday. So we can do something like this - when determining a weekday, we can use that to determine how much we need to add. If we end up with a 6 (Saturday) we want to add 2 days to push it to a Monday. In the case of a 7, we want to add 1 day to push it to a Monday. Thus, we can simply take the 8 - weekday(h10+45) to add when it's a weekday. So our add value becomes

//   determine day type   weekday  weekend, so add the offset
= if(weekday(h10+45) < 5, h10+45, h10 + 45 + (8 - weekday(h10+45))

You also have a requirement about being blank, so you'll want to wrap whatever you use with

=if(isblank(h10),"", /* your real function here */)

这篇关于确定日期是否为Excel中的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！