WORKDAY function: how does [days] work

[Jess UK]

Column A is the duration of each phase of a job in days. Column B is Start date for each phase. Column C has the following formula:
=(WORKDAY.INTL(B1,A1,11))

1 6-Mar 7-Mar
3 8-Mar 12-Mar
4 13-Mar 17-Mar
3 18-Mar 21-Mar
3 22-Mar 26-Mar
3 27-Mar 30-Mar
1 31-Mar 2-Apr


I would like a value of one in Column A to show the same date in Column C as Column B, because it only takes one day. When using the cells above to project into a calendar, 24 work days are shown, instead of 18 (the sum of Column A). I tried to rectify this by using a 'helper column' that -1 from each of the working days. This returns the dates as I would like them (start and finish dates for phase one now both being 5-Mar and so on).

0 6-Mar 6-Mar
2 7-Mar 9-Mar
3 10-Mar 14-Mar
2 15-Mar 17-Mar
2 18-Mar 20-Mar
2 21-Mar 23-Mar
0 24-Mar 24-Mar


When the data is projected into the calendar if the start day is a Sunday e.g March 18, things get thrown off. A day is missed. The calendar says 2 days worked, 19th and 20th when as you can see above the phase is actually supposed to take 3 working days.

The formula I am using in for the calendar projection is:
=IF(AND(V$2>=$G16,V$2<=$H16),$A16,"")
where V2 is the date in the calendar, G is the start date, H is the end date as calculated above and A is the information to be displayed in the calendar for that day.

Any ideas? If you need to see more of the spreadsheet let me know.

 

[xenou]

According to this article, if you use 11 as the third argument to Workday.Intl, it excludes sunday. That is why sunday is not counted.

http://www.excelfunctions.net/Excel-Workday-Intl-Function.html

 

[Jess UK]

Let me use just three of the rows to make my question clearer:

Duration Start Date End Date
1 6-Mar 7-Mar
3 8-Mar 12 -Mar
3 18-Mar 21-Mar

For the first row I want the formula =(WORKDAY.INTL(B1,A1,11) to return 6-Mar as the end date. It only takes one work day and March 6 is not a Sunday.

For the second row 3 working days would be March 8-10. Sunday March 11 shouldn't affect the formula but for some reason it gives the end date as Monday 12.

For the second row, as March 8 is a Sunday, the formula puts an end date of March 21 to give 3 working days (Monday-Wednesday). This is what I want.

So sometimes it returns the value I want and other times not, depending if/when a Sunday falls.

 

[RonB1111]

If both Sat and Sun are non-workdays, try this:
=IF(WEEKDAY(B1,2)>5,WORKDAY(B1,A1),WORKDAY(B1,A1-1))

If only Sun is a non-workday, try this:
=IF(WEEKDAY(B1,2)>6,WORKDAY(B1,A1),WORKDAY(B1,A1-1))

 

[Jess UK]

Sunday is the only non-work day.

This is what happens with the second formula:

1 5-Mar 5-Mar
3 6-Mar 8-Mar
4 9-Mar 14-Mar
3 15-Mar 19-Mar
3 20-Mar 22-Mar
3 23-Mar 27-Mar
1 28-Mar 28-Mar

It still gives me a total of 21 work days when the total of Column A is only 18. For example, the third row end date should be 13-Mar.

Thanks

 

[RonB1111]

=b1+if(weekday(b1,2)+a1-1<7,a1-1,a1-1+int((weekday(b1,2)+a1-1)/7))

 

[Jess UK]

Seems to work perfectly, thanks a million. Just to throw a spanner in the works - is there a way to set a block of dates as a holiday? If not I can manually input this. There is only one in our current work period as far as I'm aware. Thanks again.

 

[RonB1111]

The final formula used just "Weekday()" which has no provision for holidays. The earlier Workday() formula does allow excluding holidays but unfortunately I was unable to get that formula to work properly in your situation because Workday() excludes both Sat and Sun while your situation has Sun as the only non-workday.

 

[barry houdini]

Try this version

WORKDAY.INTL(B1-1,A1,11)

You can add a holiday range as 4th argument

 

[RonB1111]

Barry,
It appears WORKDAY.INTL is new to 2010. I have 2007 - maybe I should consider upgrading. Do you know of any way to get it to work in 2007. Jess orignally used it with the -1 in a helper column but he said Sunday start dates were the problem then. I think it would work like this but I'm unable to test it:
IF(weekday(B1)=1,WORKDAY.INTL(B1,A1,11),WORKDAY.INTL(B1-1,A1,11))