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.

 

[barry houdini]

Hello Ron,

I believe the -1 was being applied to the number of days and in that scenario it didn't work when the start date was a Sunday..... but the trick is to apply -1 to the start date, that should work in every situation, whether the start date is a Sunday or not.

That will give the following results which I believe are the required ones

<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=232 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c4d79b" width=64 height=20>Duration</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b" width=83>Start Date</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b" width=85>End Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c4d79b" height=20>1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">6-Mar-12</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">6-Mar-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c4d79b" height=20>3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">8-Mar-12</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">10-Mar-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c4d79b" height=20>3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">18-Mar-12</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c4d79b">21-Mar-12</TD></TR></TBODY></TABLE>

The above uses the following formula for End Date

=WORKDAY.INTL(B2-1,A2,11)

The principle being that by subtracting 1 the start date will be counted as the first workday, if it's a working day, but not if it's a Sunday

To do the same in earlier versions is not simple, this formula should do it, though

=B2-1-WEEKDAY(B2-1,3)+INT(7/6*(A2+MIN(5,WEEKDAY(B2-1,3))))

 

[RonB1111]

Thank you Barry. Jess will hopefully confirm it with his data & then he'll be able to plug in holidays. In 2007 I was able to get it to work with a formula similar to yours, but it was quite complicated and a lot of trial & error before I got it to work.

 

[barry houdini]

I'm not sure your version will work for all values, e.g. if the start date is a Saturday and the days to add is 8 your formula returns a Sunday date

 

[RonB1111]

You're right Barry.