WORKDAY function: how does [days] work

Jess UK

New Member
Joined
Mar 6, 2012
Messages
4
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top