The Analysis Toolpack provides the function NETWORKDAYS to find the number of working days between two dates. From Help:
NETWORKDAYS(start_date,end_date,holidays)
To find the number of working days in a month, given any day in that month, you can use this formula
To get the start date, the DATE function is used with the month and year of the given date and 1 for the day argument. For the end date, the DATE function is again used. The 0th (zeroth) day of the next month is the last day of the current month, so 1 is added to the month argument and zero is used for the day. The Holiday range is reference and, being in the US, I have only one holiday this month.
What if I want the number of working days relative to the month for the date in any given cell — different dates?
Sorry, unnecessary question. The answer is above!
Dick, today I faced an interesting problem. What I wanted to obtain: with one button-click, increase or decrease the date in a cell with exactly one month. I solved it by creating two buttons, one for the increasing, the other for the decreasing. The formula in cell C1 is:
=date(2004;c3;1), with C3 containing an integer, the value of which each buttonclick increases or decreases with one. A problem remains, however. If you go on clicking too much, the time period can get completely out of range, and it needs to be limited to one year. How could I allow a user to click as much as he likes and at the same time keep him within the first and the 12th month? In other words, maybe it would be more sensible to have only one button, which allows you to run through the same period perpetually, month by month, at month 12 switching back to 1. A Loop or a For Each Next construct will display all 12 months in quick succession, so that’s not what I want.
I use this formula extensively, and find that adding “-1? to the end of the formula gives a truer count of business days. For example, NETWORKDAYS would provide a result of “2? between a Monday and a Tuesday, but the actual business days elapsed is “1.” Mine would look like this:
=NETWORKDAYS(start_date,end_date,holidays)-1
Also, if your filling down this function for multiple rows, be sure to make the holidays range as an absolute range ($A$2:$A$30). Otherwise, the filldown won’t work properly.
Thoughts?
I use this formula extensively, and find that adding “-1? to the end of the formula gives a truer count of business days.
I agree completely. I almost never use this function without subtracting a day from it.
Frank, why not put an If-Then statement in your button code?
Dick – I tend to get flustered when working on someone’s machine that does not have the Analysis ToolPak installed. I use the Convert and NetworkDays functions so often that I’ve come to rely quite heavily on them.
Frank: Sounds like a good job for a spin button control. Set the Min and Max properties to 1 and 12 respectively and link it to C3.
Frank: Sounds like a good job for a spin button control. Set the Min and Max properties to 1 and 12 respectively and link it to C3.
Ooh, good call. That’s not a tool that I’ve used much, since I don’t have much need for incrementing/decrementing. However, it is the right tool for this job.
Frank, the spin button control will work well, unless you want the capability to go from 12 to 1 with a click of the increase button (or from 1 to 12 with a click of the decrease button). If that’s what you’d like to do, try this code:
Option Explicit
Public myRange As Range
Sub increase()
‘ Called with the increase button
Call spinner_2(True)
End Sub
Sub decrease()
‘ Called with the decrease button
Call spinner_2(False)
End Sub
Sub spinner_2(increase As Boolean)
‘ The “test” range is whatever cell you’d like to alter with the code — you
‘ don’t have to give it a range name, but it makes it easier to move stuff
‘ around later and not have to change your code.
Set myRange = Range(“test”)
If increase = True Then
If myRange.Value < 12 Then
myRange.Value = myRange.Value + 1
Else
myRange.Value = 1
End If
Else
If myRange.Value > 1 Then
myRange.Value = myRange.Value – 1
Else
myRange.Value = 12
End If
End If
End Sub
When used in conjunction with two buttons, just like you’ve described above, it works. It’s pretty crude, but it gets the job done.
Thank you both very much, Dick and Kevin – as an Excel novice I had never used a spinbox before. Also, to be honest, my question wasn’t really related to networkdays, except that I do all this to the benefit of my department which is called Networks. Kevin, the trick with the perpetual 1-to-12 cycle is absolutely amazing.
If my Excel skills keep icreasing as they do, my Networkdays might soon be numbered!
I have sent Microsoft a problem report on this function. They define the function as returning the number of business days between two dates when it doesn’t. My math (before new math) would calculate 0 days if you enter the same date, 1 day for date 2 after date 1 and -1 for date 2 before date 1. The necessity of subtracting 1 from the function is fine when the number of days is positive but becomes a problem when it is negative (-1 – 1 = -2). My application has negative and positive values and I had to adjust the networkdays results in order to make it work.
The real issue is the function does not do what the function definition claims and apparently has been that way since day one. No mention is made on the knowledge base of the problem. It will be interesting to see if I get a response from Microsoft.
I’m using the networkdays function to pull together some information on whether we met our goal with regard to responses from inquiries we receive. We try to respond to inquiries within a certain amount of time based on business days, so I need to eliminate weekends.
I have a simple spreadsheet with three columns: start date, end date and the formula. The start and end dates have no time associated with them, so they are all set so that the time 12AM. I’m not incorporating any holidays into the formula at the moment, but I know how to when needed.
The formula for the first row of data reads:
=networkdays(a2,b2)
The results that I get back are interesting to say the least, and I found the earlier discussions re” networkdays on this board through a google search and thought I would follow up to see if you all had any idea what was going on.
I didn’t understand the results that I was getting, so I started looking into it more closely. I’ve included sample data and results below.
The pattern seems to indicate that it correctly counts the net workdays on Saturdays and Sundays, but does not on Mon – Fri (or the other way around depending on whether you expect it to count the start day or not).
The results seem to get even more confused when there is time between the start and end dates. To examine this, I took the same start dates and then incremented them by 2 days to determine the end date. In that case the formula returns results that are accurate approximately 30-40% of the time, again depending how you are expecting it to report results, inclusive of the start day or not.
I just wanted to post this to see if anyone else had encountered this, or is there something that I’m doing wrong? I also did test using different time stamps, and in the case of where the start and end date were on the same day, it didn’t seem to have any impact on the result if I made the elapsed time in hours greater or less than 12, but the time stamp did seem to impact the results in the cases where the start and end date were different. I’m looking forward to any advice or feedback anyone can provide, thanks!
start end formula result (days)
1/01/2004 1/01/2004 1.00
1/02/2004 1/02/2004 1.00
1/03/2004 1/03/2004 0.00
1/04/2004 1/04/2004 0.00
1/05/2004 1/05/2004 1.00
1/06/2004 1/06/2004 1.00
1/07/2004 1/07/2004 1.00
1/08/2004 1/08/2004 1.00
1/09/2004 1/09/2004 1.00
1/10/2004 1/10/2004 0.00
1/11/2004 1/11/2004 0.00
1/12/2004 1/12/2004 1.00
1/13/2004 1/13/2004 1.00
1/14/2004 1/14/2004 1.00
1/15/2004 1/15/2004 1.00
1/16/2004 1/16/2004 1.00
1/17/2004 1/17/2004 0.00
1/18/2004 1/18/2004 0.00
1/19/2004 1/19/2004 1.00
PLEASE disregard my last post!! I’m a complete Idiot!
The results posted are exactly as they should be. What I posted in my example was fake data to help me try to understand what I was encountering with my real data. A few minutes into my bike ride on my way home last night I realized my mistake. In my real data there happen to be requests received and completed over the weekend because one of the ways we receive inquiries is through an automated system, and I didn’t realize this. In my attempt to diagnose the problem, I used the weekday function, but with the wrong option so it appeared to me that I was still getting incorrect results.
Sorry for the confusion!
Thanks, Michelle
Hello there,
Can someone help me change the weekend days from Saturday-Sunday to Friday-Saturday?
I need to use it with the NETWORKDAYS()formula.
Buy the way – I’m from Israel…
Hi
one way:
=NETWORKDAYS(A1+1,A2+1)
where A1 and A2 store your start and ending dates
Frank
I need help on conditional formatting .
Ex: If I enter anywhere in a row/column a word “love” then the entire rows color should be changed to Red or any other color instead of just one cell which contains the word love.
can this be acheived using only conditional formatting.
Hi,
I’m a bit stuck with the whole NETWORKDAYS function. I need to report over a 6-day work period (Mon – Fri) and the funcion doesn’t support this. How can I include Saturdays in the calculation to determine how many days pass?
I have looked at Frank’s solution above which seems to make sense, but I can’t get it to work.
Thanks
Clen
Actually, my post above – my work period is Monday through Saturday (that would be 6 days) :)
doh!
Hi,
How to change weekdays from MON-FRI to Saturday – Thursday (in this part of the world weekend is on Friday)?
NETWORKDAYS function when used takes Saturday and Sunday as holidays and thus resulting wrong calculation in our ELAPSED TIME for a process.
START TIMEEND TIMEELAPSED TIME
4/28/2005 13:274/30/2005 9:0043:32:32
Process started on 28th April(Thursday). 29th is Holiday being weekend (Friday). Process completed on 30th (Saturday. Elapsed time is more than actual time taken for the process.
Can somebody help us on this issue.
Pradeep, 5/3/’05
This function is designed to exclude Saturday and Sunday as the weekend. In all versions of Microsoft Excel including version 4.0 the following formula can be used to find the number of weekdays between two dates, where Thursday and Friday are the weekend:
=A2-A1+1-INT((A2-A1)/7)*2-IF(AND(WEEKDAY(A2)7,WEEKDAY(A1)7,WEEKDAY(A2)6),2,IF(AND(WEEKDAY(A2)=7,WEEKDAY(A1)
Does anyone know how to go backwards with the Networkdays function? For example, if today is 11/01/05, how would I go back and say give me 5 Networkdays ago’s date? This would be very helpful in determining standards, so I need the system to bring back 10/25/05, which is when the request came in. Thanks much!
Drewsky, just do =WORKDAY(A1,-5,holidays) where A1 is today’s (or whichever other) date, and holidays is your list of holiday dates.
Dick – I want to determine the workdays that fall within a range of workdays. i.e billing period is 12/16/05 and 1/15/06 – the workdays in the period is 1/3/06 through 1/6/05. So within the 21 days, 4 days were billable. (did not include holidays). Thanks
Beth: That question doesn’t make any sense. :) Sorry. Send me an email with more details.
Hi – Hopefully this will align properly. The billing period runs from the 16th to the 15th of the following month. So for the PPI phase we will bill 4 day, since it falls within the 12/16 to 1/16 range it is 4 days. The SRA phase crosses over so 3 days are before Jan 15th and 3 days after. Likewise the SDS phase, the 1st 20 days are in the 1/16 -2/15 billing period and the last 5 days are in the following period. I know the Phases and the amount billed, I just want to populate the days billed into the proper periods.
Bill 12/16- 1/16- 2/16-Project Phase 1/15 2/15 3/15
PPI5-Jan10-Jan 4 4
SRA11-Jan18-Jan 6 33
SDS19-Jan22-Feb 25 205
Some one can help me, the problem is my labor week Is monday through saturday instead friday , i need a function that be able to return the working days
Hi everyone,
I am stuck trying to creat a “perfet” holiday organizer for my call centre team.
I am a team leader responcible for all the Holiday’s taken within the year so all the work I do has to be 100% accurate.
I want to create a formula which will automaticaly calculate the amount of WORKING days used for Holidays. EXCLUDING saturday and sunday as days that are anyway roster off.
So what I did is:
End-Date MINU Start-Date = Day’s taken.
For example:
Start End Amount of days
02/01/200606/01/20064
BUT what if I have a period from
03/01/200627/01/2006
which is obviously a period were 6 weekend days come into play. So I need to find out how to create a formula SUBSTRACTING Saturdays and Sundays automaticaly.
I am not getting any smarter also reading the networkday comments.
Please help…
Thanks
From: http://www.cpearson.com/excel/DateTimeWS.htm
You can use the following ARRAY FORMULA to compute the number of Saturdays(value in C2=7) from start date(in A2) to end date(in B2):
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT(“1:”&TRUNC(B2-A2)+1)))=C2,1,0))
This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,…,7=Saturday)
So to compute the number of workdays and include Saturday, use:
=NETWORKDAYS(A2,B2)+1+SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT(“1:”&TRUNC(B2-A2)+1)))=7,1,0))
*NOTE: You _MUST_ hit Ctrl+Shift+Enter, rather than just Enter for the ARRAY FORMULA to work!!
For more info on array functions see: http://www.cpearson.com/excel/array.htm
I’m having an issue with NETWORKDAYS inside of an array formula. The formula is supposed to find all rows in the array that meet the specified criteria and produce a SUM of all the NETWORKDAYS in the array. If the total number of NETWORKDAYS is 0, nothing is displayed, otherwise the SUM of the NETWORKDAYS divided by another Cell is displayed. The formula below returns a #VALUE! error:
{=IF(SUM(IF(SourceData!$C$2:$C$1000=”Criteria1?,IF(SourceData!$D$2:$D$1000=”Criteria2?,IF(SourceData!$H$2:$H$1000=”Criteria3?,NETWORKDAYS(SourceData!$J$2:$J1000,SourceData!$K$2:$K$1000),0),0)))= 0,””,SUM(IF(SourceData!$C$2:$C$1000=”Criteria1?,IF(SourceData!$D$2:$D$1000=”Criteria2?,IF(SourceData!$H$2:$H$1000=”Criteria3?,NETWORKDAYS(SourceData!$J$2:$J$1000,SourceData!$K$2:$K$1000),0),0)))/$K$37)}
I could create an additional column to store the NETWORKDAYS for each item in the array; However the best solution is to have the formula handle it for the entire range as the data is constantly replaced from an exported report overwriting the SourceData! worksheet.
This array formula works perfectly when used (below) to simply sum a range contained in another worksheet and is only a problem with using the NETWORKDAYS function.
{=IF(SUM(IF(Pipeline!$C$2:$C$1000=”Criteria1?,IF(Pipeline!$D$2:$D$1000=”Criteria2?,IF(Pipeline!$K$2:$K$1000=”Criteria3?,Pipeline!$P$2:$P$1000,0),0)))= 0,””,SUM(IF(Pipeline!$C$2:$C$1000=”Criteria1?,IF(Pipeline!$D$2:$D$1000=”Criteria2?,IF(Pipeline!$K$2:$K$1000=”Criteria3?,Pipeline!$P$2:$P$1000,0),0)))/$K$5)}
Any insight or suggestions would be most appreciated.
Thanks,
Walt
Walt,
NETWORKDAYS do not accept ranges, it only works on individual cells.
As an exemple, NETWORKDAYS (A:A;B:B) returns a #NUM error. The same happens with named ranges.
I’ve found no other solution than to calculate cell by cell or to replace NETWORKDAYS by a formula.
>>If my Excel skills keep icreasing as they do, my Networkdays might soon be numbered
The number of my remaining Networkdays is now exactly 18 networkdays. I’ll be involved in risk assessment and market data in a large dealing room.
hi
ive made a calculator to calculate TAT’s by entering Start Date and End Date. Excel has a function “networkdays” which is what i’ve used. the problem is that we run on a Fri/ Sat weekend, and this formula takes a Sat/ Sun weekend, for which i’ve offset the start date and end date by one day each. it works fine till i don’t define a holiday list. Once i do that, some bug creeps in, something to do with sundays. appreciate any help!
To define my problem clearly
1) The Base Formula Calculates Working Dates inclusive of both Start Date and End Date. Weekend is Sat/ Sun: =NETWORKDAYS(Start Date,End Date,Holiday List)
2) To exclude either Start Date/ End Date: =NETWORKDAYS(Start Date,End Date,Holliday List) -1
3) To Use Fri/ Sat Weekend: =NETWORKDAYS(Start Date +1,End Date+1) (Does not support Holiday List, am working on it)
4) To Use Thu/ Fri Weekend: =NETWORKDAYS(Start Date +2,End Date+2) (Does not support Holiday List, am working on it)
I need it to support the holidays i define as well, it works fine until the holiday i define falls on a sunday or a friday. :(
is there any alternative to calculate working days, my constraint is that i HAVE to use excel.
I’m a newbie with Excel. I need a formula to calculate the end date given the elapsed weekdays and a start date.
Estimated elapsed days | Start date | Finish date
13 2007-03-28 YYYY-MM-DD
Any help appreciated,
Frank
Frank, refer item #21 above.
The =Workday function is the one you need.
You will need the Analysis Toolpack add-in installed.
I’m just having no luck trying to get this calculation to work. Basically I’m trying to count the number of workdays someone has been sick. I have the first day of absence in DDMMYY in one column (D3) and then todays date in E3 (using the =NOW() function). But whenever I try to use the Network days function, i.e. =NETWORKDAYS(D3,E3) , I just get a #NAME? error. If I do Insert > Name > Define and put Networkdays, I get a #REF! error.
I am using Excel 2000. Any help would be greatly appreciated! :)
Hi Frank,
I just had a question- i have a month array in excel wherein in need to eliminate the weekends and list of holidays (which changes every year).
What is the best way to do it and the code for it?
Any help would be greatly appreciated..
Hi there,
I need to know how to go about setting this up:
I have a start date in column A and need to return an end date which occurs 7 days later, in column B. Once I have inputed the start date in Column A and pressed Enter, I want column B to automatically update with the end date of 7 days later. How do I go about doing that?
Any help is appreciated, thanks!
Audrey
Need a formula that will identify a date as a holiday labeled as text = “H”.
For example:
If in cell “A1? there is a date such as 12/25/CCYY, cell “B1? will list H. I need the formula to recognize all US major holidays. Thanks.
Any idea on how to calculate NETWORKDAYS in VBA?
I need to get all working days from example (5/11 to 6/27) in VBA
Any help???
Prakash
Public Function BizDateDiff(ByVal varDateStart As Date, ByVal varDateEnd As Date, DayNumber ) As Integer ‘ DayNumber (sunday =1,monday=2…)
Dim varNextDate As Date
‘This function calculates the weekdays between two dates.
‘Exit if variables not a valid date
If Not IsDate(varDateStart) Or Not IsDate(varDateEnd) Then
BizDateDiff = 0
Exit Function
End If
varNextDate = varDateStart
BizDateDiff = 0
While Not varDateEnd
Manoj’s code has been truncated. Can we see the rest?
Maybe
‘ DayNumber (sunday =1,monday=2…)
Dim varNextDate As Date
‘This function calculates the weekdays between two dates.
‘Exit if variables not a valid date
If Not IsDate(varDateStart) Or Not IsDate(varDateEnd) Then
BizDateDiff = 0
Exit Function
End If
varNextDate = varDateStart
BizDateDiff = 0
While Not varDateEnd 1 And Weekday(varNextDate) 7 Then
BizDateDiff = BizDateDiff + 1
End If
varNextDate = varNextDate + 1
Wend
End Function
The code is being corrupted. It sould read
Public Function BizDateDiff(ByVal varDateStart As Date, ByVal varDateEnd As Date, DayNumber) As Integer
‘ DayNumber (sunday =1,monday=2…)
Dim varNextDate As Date
‘This function calculates the weekdays between two dates.
‘Exit if variables not a valid date
If Not IsDate(varDateStart) Or Not IsDate(varDateEnd) Then
BizDateDiff = 0
Exit Function
End If
varNextDate = varDateStart
BizDateDiff = 0
While Not varDateEnd 1 And Weekday(varNextDate) 7 Then
BizDateDiff = BizDateDiff + 1
End If
varNextDate = varNextDate + 1
Wend
End Function
Still not right! I don’t know about escape characters so you can see the code here.
http://vbaexpress.com/forum/showthread.php?t=18716&page=2
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?
How i can applay this formula where Friday,Sat are the weekend
A2-A1+1-INT((A2-A1)/7)*2-IF(AND(WEEKDAY(A2)7,WEEKDAY(A1)7,WEEKDAY(A2)6),2,IF(AND(WEEKDAY(A2)=7,WEEKDAY(A1)
try this – this code works out working days accurately – tested it on thousands of dates and gives same result as network days
=INT(((TODAY())-D1)/7)*5-IF(WEEKDAY(D1,2)>5,5,WEEKDAY(D1,2)-1)+IF(WEEKDAY((TODAY()),2)>5,5,WEEKDAY((TODAY()),2))+5
replace:
(today()) with latest date
and
d1 with earliest date
many thanks
your resident excel guru!
Odd problem:
The formula in my cell read: =NETWORKDAYS(I2,J2)
I2 = 8/4/2008
J2 = 9/24/2008
The value returned = 912
The value I’m looking for should be about 51.
Any help?
As it often happens, I solved my own problem within seconds of posting it here.
I had the Number format as [h] … 912 hours occurs in … 38 days. Once I change the format of the cell to show a whole number, the correct value was displayed.
Follow up on the network days function. Any way to automatically calculate the current work day? I have the formula to calculate the number of work days in a month but would like to also display the current work day out of the total work days in a month based off the current date.
In other words, based off the start date of 11/1/2008 and end date of 11/30/2008 and two holidays 11/27/2008,11/28/2008, I can calculate the number of work days to be 18. Any way to automatically calculate which current workday I am on based on my system time.
Change End Date for Today().
Regards
My working shift is 4 days ON and 4 days OFF, how can i create a formula to calculate the days worked for the whole year,and also to find out what days of the week i will be working. please help thank you.
I know this is a very simple question, but cannot figure it out with Excel’s help.
I have a starting date
I want to add 1 business day & 2 business days to this – I have that figured out with workday and adding holidays.
But then to this starting day I want to add 30 days & 45 days. Not workday – but actual days. If the calculated date falls on a weekend, I want a formula to move this to the next business day.
Can you help me with this?
Try this, you can even use 0 work days for fun or 7 work days a week, specify the first day of the work week and number of holiday days, it will give you total workdays
Public Function BizDateDiff(ByVal varDateStart As Date, ByVal varDateEnd As Date, FirstDayNumber, WkDaysInWk, Holidays) As Integer
‘ DayNumber (sunday =1,monday=2…)
Dim varNextDate As Date
Dim WkEnd1, WkEnd2, WkEnd3, WkEnd4, WkEnd5, WkEnd6, WkEnd7 As Integer
‘This function calculates the weekdays between two dates.
‘Exit if variables not a valid date
If Not IsDate(varDateStart) Or Not IsDate(varDateEnd) Or WkDaysInWk 7 Or FirstDayNumber 7 Then
BizDateDiff = 0
Exit Function
End If
Select Case WkDaysInWk
Case 7
WkEnd1 = 0
WkEnd2 = 0
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 6
Select Case FirstDayNumber
Case 1
WkEnd1 = 7
WkEnd2 = 7
WkEnd3 = 7
WkEnd4 = 7
WkEnd5 = 7
WkEnd6 = 7
WkEnd7 = 7
Case 2
WkEnd1 = 1
WkEnd2 = 1
WkEnd3 = 1
WkEnd4 = 1
WkEnd5 = 1
WkEnd6 = 1
WkEnd7 = 1
Case 3
WkEnd1 = 2
WkEnd2 = 2
WkEnd3 = 2
WkEnd4 = 2
WkEnd5 = 2
WkEnd6 = 2
WkEnd7 = 2
Case 4
WkEnd1 = 3
WkEnd2 = 3
WkEnd3 = 3
WkEnd4 = 3
WkEnd5 = 3
WkEnd6 = 3
WkEnd7 = 3
Case 5
WkEnd1 = 4
WkEnd2 = 4
WkEnd3 = 4
WkEnd4 = 4
WkEnd5 = 4
WkEnd6 = 4
WkEnd7 = 4
Case 6
WkEnd1 = 5
WkEnd2 = 5
WkEnd3 = 5
WkEnd4 = 5
WkEnd5 = 5
WkEnd6 = 5
WkEnd7 = 5
Case 7
WkEnd1 = 6
WkEnd2 = 6
WkEnd3 = 6
WkEnd4 = 6
WkEnd5 = 6
WkEnd6 = 6
WkEnd7 = 6
End Select
Case 5
Select Case FirstDayNumber
Case 1
WkEnd1 = 6
WkEnd2 = 7
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 2
WkEnd1 = 7
WkEnd2 = 1
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 3
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 4
WkEnd1 = 2
WkEnd2 = 3
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 5
WkEnd1 = 3
WkEnd2 = 4
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 6
WkEnd1 = 4
WkEnd2 = 5
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 7
WkEnd1 = 5
WkEnd2 = 6
WkEnd3 = 0
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
End Select
Case 4
Select Case FirstDayNumber
Case 1
WkEnd1 = 5
WkEnd2 = 6
WkEnd3 = 7
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 2
WkEnd1 = 6
WkEnd2 = 7
WkEnd3 = 1
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 3
WkEnd1 = 7
WkEnd2 = 1
WkEnd3 = 2
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 4
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 5
WkEnd1 = 2
WkEnd2 = 3
WkEnd3 = 4
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 6
WkEnd1 = 3
WkEnd2 = 4
WkEnd3 = 5
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 7
WkEnd1 = 4
WkEnd2 = 5
WkEnd3 = 6
WkEnd4 = 0
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
End Select
Case 3
Select Case FirstDayNumber
Case 1
WkEnd1 = 4
WkEnd2 = 5
WkEnd3 = 6
WkEnd4 = 7
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 2
WkEnd1 = 5
WkEnd2 = 6
WkEnd3 = 7
WkEnd4 = 1
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 3
WkEnd1 = 6
WkEnd2 = 7
WkEnd3 = 1
WkEnd4 = 2
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 4
WkEnd1 = 7
WkEnd2 = 1
WkEnd3 = 2
WkEnd4 = 3
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 5
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 6
WkEnd1 = 2
WkEnd2 = 3
WkEnd3 = 4
WkEnd4 = 5
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
Case 7
WkEnd1 = 3
WkEnd2 = 4
WkEnd3 = 5
WkEnd4 = 6
WkEnd5 = 0
WkEnd6 = 0
WkEnd7 = 0
End Select
Case 2
Select Case FirstDayNumber
Case 1
WkEnd1 = 3
WkEnd2 = 4
WkEnd3 = 5
WkEnd4 = 6
WkEnd5 = 7
WkEnd6 = 0
WkEnd7 = 0
Case 2
WkEnd1 = 4
WkEnd2 = 5
WkEnd3 = 6
WkEnd4 = 7
WkEnd5 = 1
WkEnd6 = 0
WkEnd7 = 0
Case 3
WkEnd1 = 5
WkEnd2 = 6
WkEnd3 = 7
WkEnd4 = 1
WkEnd5 = 2
WkEnd6 = 0
WkEnd7 = 0
Case 4
WkEnd1 = 6
WkEnd2 = 7
WkEnd3 = 1
WkEnd4 = 2
WkEnd5 = 3
WkEnd6 = 0
WkEnd7 = 0
Case 5
WkEnd1 = 7
WkEnd2 = 1
WkEnd3 = 2
WkEnd4 = 3
WkEnd5 = 4
WkEnd6 = 0
WkEnd7 = 0
Case 6
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 0
WkEnd7 = 0
Case 7
WkEnd1 = 2
WkEnd2 = 3
WkEnd3 = 4
WkEnd4 = 5
WkEnd5 = 6
WkEnd6 = 0
WkEnd7 = 0
End Select
Case 1
Select Case FirstDayNumber
Case 1
WkEnd1 = 2
WkEnd2 = 3
WkEnd3 = 4
WkEnd4 = 5
WkEnd5 = 6
WkEnd6 = 7
WkEnd7 = 0
Case 2
WkEnd1 = 3
WkEnd2 = 4
WkEnd3 = 5
WkEnd4 = 6
WkEnd5 = 7
WkEnd6 = 1
WkEnd7 = 0
Case 3
WkEnd1 = 4
WkEnd2 = 5
WkEnd3 = 6
WkEnd4 = 7
WkEnd5 = 1
WkEnd6 = 2
WkEnd7 = 0
Case 4
WkEnd1 = 5
WkEnd2 = 6
WkEnd3 = 7
WkEnd4 = 1
WkEnd5 = 2
WkEnd6 = 3
WkEnd7 = 0
Case 5
WkEnd1 = 6
WkEnd2 = 7
WkEnd3 = 1
WkEnd4 = 2
WkEnd5 = 3
WkEnd6 = 4
WkEnd7 = 0
Case 6
WkEnd1 = 7
WkEnd2 = 1
WkEnd3 = 2
WkEnd4 = 3
WkEnd5 = 4
WkEnd6 = 5
WkEnd7 = 0
Case 7
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 0
End Select
Case 0
Select Case FirstDayNumber
Case 1
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 2
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 3
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 4
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 5
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 6
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
Case 7
WkEnd1 = 1
WkEnd2 = 2
WkEnd3 = 3
WkEnd4 = 4
WkEnd5 = 5
WkEnd6 = 6
WkEnd7 = 7
End Select
End Select
varNextDate = varDateStart
BizDateDiff = 0
While Not (varDateEnd = varNextDate)
If Not (Weekday(varNextDate) = WkEnd1 Or Weekday(varNextDate) = WkEnd2 Or Weekday(varNextDate) = WkEnd3 Or _
Weekday(varNextDate) = WkEnd4 Or Weekday(varNextDate) = WkEnd5 Or Weekday(varNextDate) = WkEnd6 _
Or Weekday(varNextDate) = WkEnd7) Then
BizDateDiff = BizDateDiff + 1
End If
varNextDate = varNextDate + 1
Wend
BizDateDiff = BizDateDiff – Holidays
End Function
I need help on conditional formatting .
(date+2 days) – weekends.
Please advise.
Hi,
for a particular scheduling worksheet I am developing, I need to to add the net working days between two dates (forecast start & end) to a later (or earlier)actual start date to get an indicative end date. I am finding that inputing the output of Networkdays into the Workday function does not appear to give me the correct answer when the start of the workday period is itself a workday, see example below. However, if the 1st day of the new period is a weekend or holiday the answer is correct.
Can anyone assist as to whether this is a misuse of these functions, a malfunction or Networkdays includes the first day in its calculations whereas Workday does not (and where that behaviour is documented?
NetWorkdays
Start 1/08/2012 0:001/09/2012 0:00
End 3/08/2012 0:003/09/2012 0:00
Public holidaysPublic holidaysPublic holidays
Worksdays 3 1
Workday
Start 1/08/2012 0:001/09/2012 0:00
Workdays 3 1
Public HolidaysPublic holidaysPublic holidays
End 6/08/2012 0:003/09/2012 0:00
FALSE TRUE
Thanks.
Andy: I’m not sure what your examples are showing. If I have 1/8/2012 in A1 and 3/8/2012 in A2, then this formula works
Thanks for looking into this for me Dick. Yes my examples weren’t too clear, the formatting got garbled in the posting.
I’ve used your exact formula and get the following results (my date format is dd/mm/yyyy):
A1 (C259): 01/08/2012
A2 (C260): 03/08/2012
Result: 06/08/2012
A1 (C259): 01/09/2012
A2 (C260): 03/09/2012
Result: 03/09/2012
I used =WORKDAY(C259,NETWORKDAYS(C259,C260,PublicHolidays),PublicHolidays)for both caluations. I also tried it with and without public holidays, same answer.
I don’t understand why I get different results to you (I am using Excel 2010 under Windows 7) nor why I get a sdifferent result if the start date is a workday or not.
Regards.
Andy
Andy: OK, I get it. The problem is that NETWORKDAYS is inclusive – includes both the starting and ending date – while WORKDAY begins counting from the start day. When you do 1-Aug to 3-Aug, NWD returns 3. When you input 3 into WORKDAY, it adds 3 to 1-Aug to get 4-Aug. Since 4-Aug is a Saturday, it jumps ahead to 6-Aug.
For 1-Sep to 3-Sep, NWD returns 1 (3-Sep is the only workday in that range). When you add 1 to 1-Sep you get 2-Sep. Since 2-Sep is a Sunday, it jumps ahead to 3-Sep.
If you use NETWORKDAYS as an input to WORKDAY, you have to subtract 1.
Thanks Dick,
that is the conclusion wrt the operation of the 2 functions that I had reached. However, if I apply the proposed solution to the second of my 2 dates (01/09/2012) I end up with a date difference of 0 (1-1) which gives me back 01/09/2012 rather than 03/09/2012.
It appears the 1 must be substracted iff the first day of the period is a working day! It seems very strange to me.
Regards.
Andy
Andy (Comment 59) and Scott (Comment 4):
FWIW:
I’ve run into this need to occasionally subtract 1 from the calculated NetWorkDays value, and think that Andy’s comment (“It appears the 1 must be substracted iff the first day of the period is a working day!”) is correct. If the start_date is in cell E1, the end_date is in cell F1, and the Holidays are contained in a Range of the same name, the following formula seems to adjust accordingly:
=IF(AND(TEXT(E1,”DDD”)”Sat”,TEXT(E1,”DDD”)”Sun”),NETWORKDAYS(E1,F1,Holidays)-1,NETWORKDAYS(E1,F1,Holidays))
(I’ve tested this for the months of January, February, March and April 2012 with the only Holiday in this period being 1/2/2012, and returned correct values of 21, 21 22 and 21 respectively)
I intitially thought that I would have to subtract 1 if the start_date was NOT in the Holiday list, too (as well as NOT being a Sat or Sun) and had the following formula as a result, but it does not seem to calculate correctly or be necessary:
=IF(AND(TEXT(E1,”DDD”)”Sat”,TEXT(E1,”DDD”)”Sun”,NOT(ISNA(VLOOKUP(E1,Holidays,1,FALSE)))),NETWORKDAYS(E1,F1,Holidays)-1,NETWORKDAYS(E1,F1,Holidays))
I’d be interested if others can confirm this works correctly for them.
Jeff
Hmmm, I’ve just looked at my last posted comment (# 60), and the code I posted seems to have omitted the “Greater Than” and “Less Than” symbols that should have been there (together) preceding the “Sat” and “Sun” parameters so that the And argument is satisfied IF the start_date in cell E1 is NOT equal to “Sat” or “Sun” … (I don’t know if that was my mistake or an anomaly on this page, but I am trying to post it again here:
=IF(AND(TEXT(E1,”DDD”)”Sat”,TEXT(E1,”DDD”)”Sun”),NETWORKDAYS(E1,F1,Holidays)-1,NETWORKDAYS(E1,F1,Holidays))
(I’ve tested this for the months of January, February, March and April 2012 with the only Holiday in this period being 1/2/2012, and returned correct values of 21, 21 22 and 21 respectively)
I intitially thought that I would have to subtract 1 if the start_date was NOT in the Holiday list, too (as well as NOT being a Sat or Sun) and had the following formula as a result, but it does not seem to calculate correctly or be necessary:
=IF(AND(TEXT(E1,”DDD”)”Sat”,TEXT(E1,”DDD”)”Sun”,NOT(ISNA(VLOOKUP(E1,Holidays,1,FALSE)))),NETWORKDAYS(E1,F1,Holidays)-1,NETWORKDAYS(E1,F1,Holidays))
Again, I’d be interested if others can confirm this works correctly for them.
Ok, for whatever reason, the posted comments above (# 60 and # 61) ARE omitting the GT and LT symbols from the code I posted … for anyone trying this out, please adjust the code accordingly.
Jeff
To be clear, the “Less Than” symbol should precede the “Greater Than” symbol, together indicating that the Cell E1 parameter is NOT equal to “Sat” or “Sun”.
Jeff
Ok, I seem to have gotten myself into a twist with the issue that led to my previous 4 comments. I had been trying to calculate NetWorkDays, by month, for the year, but also making the calculation dynamic in case the user date parameters were for ANY period in the year; thus I was using some MAX and EOMonth functions in conjunction with the NetWorkDays function and it initally appeared that Scott’s comment (“It appears the 1 must be substracted iff the first day of the period is a working day!”) was correctly leading me to subtract 1 if the start_date was NOT a Saturday or Sunday. However, for my purposes, the NetWorkDays function works WITHOUT an adjustment for Subtracting 1 in ANY scenario (I just had to add 1 to the E1 value since that was an END of month date from the previous month). I’m now guessing that Scott’s comment referred to the WORKDAY function and not the NetWorkDays function. Anyway, sorry to have cluttered up this page with what turned out to be a wild goose chase. ;-)
Jeff