Determine if date is a workday in Excel

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

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

回答1:

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 */)

回答2:

You can combine the functions for IF(), WEEKDAY() and WORKDAY() to calculate your finish date and ensure that it does not fall on a weekend.

I've used

WEEKDAY(WORKDAY(H10+45),16)

to have Saturday and Sunday be represented as days 1&2 respectively.

IF(WEEKDAY(WORKDAY(H10,45),16)=1,WORKDAY(H10,45)+2,IF(WEEKDAY(WORKDAY(H10,45),16)=2,WORKDAY(H10,46),H10))