NETWORKDAYS is a function in the Analysis Toolpak that returns the number of working days between two days. If you need to find a day that is a specified number of working days away, then use the WORKDAY function (or its alternative).
This example shows the syntax for WORKDAY, but doesn’t use the optional holidays argument. The other two formulas are B3: a straight subtraction of two days and D3: Frank Kabel’s alternative array formula.
To find a day that’s a number of days away from a start date, but returning Friday if the result is Saturday and returning Monday if the result is Sunday, you can use a formula like this:
This computes the day of the week number and uses the CHOOSE function to add a day if WEEKDAY returns 1 (Sunday to Monday) and subtract a day if WEEKDAY returns 7 (Saturday to Friday).
This replacement formula is inaccurate because it doesn’t correctly account for weekends. For example, three workdays from Thursday is Tuesday, not Monday. You need to correctly factor in the number of weekend days that have past and also add this as a multiple of the number of weeks past:
=date+(num_days + 2*(num_days/5))+CHOOSE(WEEKDAY(date+num_days),1,0,0,0,0,0,2)
Also, I didn’t agree with subtracting a day if the result ended on Saturday.
The formula still isn’t correct.
It correctly adds 2 days weekend for every full 5 workdays, but to determine whether another 1 or two days need to be added at the end, only the surplus days (below full 5 workdays) may be taken into account. And no matter whether the ending day is saturday or sunday, 2 days need to be added in any case (a full weekend)
The formulat I came up is
=date+days+2*(TRUNCATE(days/5)+CHOOSE(WEEKDAY(date+MODULO(days;5));2;0;0;0;0;0;2)
I’m not sure about “truncate” and “modulo”, the German Excel uses German keywords “ganzzahl” and “rest” (how stupid!).
One could do it with a recursion too (add 2 if the result is Sa or So) by adding a recursive reference as the WEEKDAY parameter. But this is typical JAVA programmers style – nice, obvious, understandable, big, bloated and slow ;)
How do I find the first calendar that is a specified number of workdays after a specified date, taking into account of holidays?
Roopal, I have the same question. We need to be able to calculate from a date +15 days and exclude weekends and a specific list of holidays. I tried =workday(A1,+15,holidays) but got a #Name? as a result. I see above that Workday does not use the Holiday argument. So how do we step ahead and not include weekends and holidays?
Roopal, you make a list of the dates that are holidays somewhere in your workbook, name the list a range name holidays. I tried other range names and only holidays works. The formula I needed turned out to be: =WORKDAY(A2,15,holidays) where my beginning date was in cell A2 and I wanted to step forward 15 days every time. I did have to format the cell for date format. I marked the State holidays on my calendar and counted work days forward at various months of the year and it worked perfectly every time.
Jean – this didn’t work for me. And I have the Add-in installed. I am using =WORKDAY(A1,B1,G4:G17) G4:G17 are my holiday dates.
I can’t figure out how to ‘name’ the list Holidays.
I need to calculate an end date based on a start date + a specified number of days.
Normally I use =WORKDAYS(A1,5,hols), where A1 is the start date and hols is a specified named range of other holidays. This works perfectly for Europe where Sat and Sun are weekend days and therefore ignored by the formula.
However I also need to perform this calculation for China where the working week is six days (only Sunday is a day off). Does anyone know how I could either redefine the Workday function somehow, or create a different formula which would only ignore sundays plus other named holidays?
Can someone help with a workday question. I have tried variations of the workday function but I think because my cell format is TIME, it is causing an error and in addition how do I address work hours?
Row 1
Col A = Start Date/Time = 9/5/2008 4:00 PM Fri
Col B = End Date/Time = WORKDAY FORMULA GOES HERE
Col C = Duration = 1 hr 30 min
Note: Col B Cannot be a Weekend or Holiday, Workweek 8 to 5
Expected Result is: 9/8/2008 8:30 AM Mon
This is an example of what I have that is not working, Analysis ToolPak is loaded & checked, holidays is working.
IN ROW:1, COl:B =WORKDAY(A1,A1+C1,holidays)
Hello!
I’m using the =WORKDAY(B24,5,holidays) function to setup a processing calendar, does what I need it to do with the exception if the 1st of the month is a Sat or Sun. My start date is the first of the month and pay date is sixth working day of the month excluding holidays and weekends (Sat/Sun)