Solved

Excel - Find number of 'work' days between dates

Posted on 2001-06-27
41
360 Views
Last Modified: 2007-12-19
Can someone tell me how to calculate the number of work days between two dates. I want to always exclude Saturday and Sunday from this calculation.

Thank you.
0
Comment
Question by:sconnell
  • 8
  • 8
  • 6
  • +7
41 Comments
 
LVL 22

Accepted Solution

by:
Dreamboat earned 50 total points
Comment Utility
Must have Analysis toolpak addin checked under Tools-Addins

Cell a1= 6/26/2001
Cell b1= 6/26/2000
Cell c1 (must be formatted as a number, not a date) should contain:

=networkdays(a2,a1)
0
 
LVL 4

Author Comment

by:sconnell
Comment Utility
Thanks dreamboat but this spreadsheet will be shared and I cannot guarantee that everyone will have the analysis toolpak installed.

I need something that will work as a stand-alone spreadsheet and ideally without macros.

Thank you
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
Couldn't think of a way without any code...  So try this.

Function Workdays(Day1, Day2)

y = Day1
While y <= Day2
If Application.WorksheetFunction.Weekday(y, 2) <= 5 _
Then x = x + 1

y = y + 1
Wend

Workdays = x
End Function
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
Couldn't think of a way without any code...  So try this.

Function Workdays(Day1, Day2)

y = Day1
While y <= Day2
If Application.WorksheetFunction.Weekday(y, 2) <= 5 _
Then x = x + 1

y = y + 1
Wend

Workdays = x
End Function
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
Couldn't think of a way without any code...  So try this.

Function Workdays(Day1, Day2)

y = Day1
While y <= Day2
If Application.WorksheetFunction.Weekday(y, 2) <= 5 _
Then x = x + 1

y = y + 1
Wend

Workdays = x
End Function
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
I have no idea why my posts are showing more then once.  I was very careful to insure they did not.  Perhaps an admin could delete the extras?  Thanks
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
sconnell, if you _have_ to forgo the Analysis ToolPak and VBA, use these (kind) of worksheet formulas:
http://www.cpearson.com/excel/DateTimeWS.htm
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
I have no idea why my posts are showing more then once.  I was very careful to insure they did not.  Perhaps an admin could delete the extras?  Thanks
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Uziel, do you happen to click the Refresh button of your browser ? If yes, which version do you have that it does not issue a warning ? As work-around, click the "Reload Question" in the question title bar
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
I just click submit, then if it is still up when I check for a response I just hit F5 to refresh.  Let's see if this double posts.  =)
0
 
LVL 6

Expert Comment

by:blakeh1
Comment Utility
I agree with Dreamboat,
the most practical solution is to use the Analysis toolpak's Networkdays function. It does not make sense to recreate an existing function in code (especialy since user can just disable macros). The Analysis toolpak comes with excel, it is not some third party utility.It is easy enough to just go to tools, add-ins and select it.

just my opinion
0
 
LVL 4

Author Comment

by:sconnell
Comment Utility
cri, thank you but the examples on this web site do require the the Analysis pack.
uziel, your idea would work but isn't that going to be a VBA macro?
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
I agree with the others.  The toolpak is the best way to go, but my function will work and will stay with the workbook so it wont matter if your people don't have it on their system.

Don't confuse it with a subroutine.  It is a function and will show up under "User Defined" when you click the function button.
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
sconnel, doohh....goofed on this one, sorry.

I once answered a question of the same kind, the 'solution' was an auxiliary sheet with the networkdays since an arbitrary startdate. This served as table determining the difference with VLOOKUPs.  This is workable if you do not have to cover than 65'000 days and do not call it in every second cell...
If you are interested I might be able to dig it up. Using the...er...search engine of E-E shall be the atonenement for the goofing.
0
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
sconnell: Can you possibly send everyone that uses it a registry edit that'll turn the analysis toolpak on just by double-clicking a file? If so, you can turn yours on, find the registry key and export it, and email the key to your users...
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Not sure whether the Toolpak is in the standard setup.
0
 
LVL 1

Expert Comment

by:manf788265
Comment Utility
Hi sconnell,
I use the AutoOpen macro to ensure that the VBA module gets loaded whenever the file  is opened. Try this:

Sub auto_open()
     Application.OnTime Now + TimeValue("00:00:05"), "loadvba"
End Sub

Sub loadvba()
   Application.Calculation = xlManual
   AddIns("Analysis ToolPak - VBA").Installed = True
   Application.Calculation = xlAutomatic
End Sub

Regards, Manfred
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
Here is your solution:
======================
open in excel the visualbasic editor, that you can find in menu tool option macro.
Copy and past the code at the end of this text.
After that you can use it as a new function as:

Example:
=Workdays(A1,A2)
(this calculates the working days between the date in Cell "A1" and "A2".

Good luck

Function WorkDays(date1, date2)

DatDif = DateDiff("d", date1, date2, 2)
DNr1 = DatePart("W", date1, 2)
DNr2 = DatePart("W", date2, 2)
Rest = 7 - DNr1
CalcD = DatDif - Rest
Weeks = Int(CalcD / 7)
WeekDs = CalcD - (7 * Weeks)
H1 = (Weeks * 5) + WeekDs



Select Case DNr1
     Case Is = 6
        H2 = Rest - 1
     Case Is = 7
        H2 = Rest - 0
      Case Else
        H2 = Rest - 2
End Select

Select Case DNr2
      Case Is = 6
         H3 = -1
      Case Else
         H3 = 0
End Select



If DNr1 > 5 Then
    WorkDs = H1 + H2 + H3
Else
    WorkDs = H1 + H2 + H3 + 1
End If



WorkDays = WorkDs

End Function


0
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
If you dont know how to get it in an Visualbasic routine,
you can also start notepad and copy and paste the VB-sourec into a text file.
Save the textfile as "Workdays.BAS"and close notepad.
Now start Excel, start Visualbasic in menu tools option macro.
Choose from the VB menu file "file import" and import the just made "Workdays.BAS".

This macro will be saved together with your worksheet.


Regards.
0
 
LVL 1

Expert Comment

by:Uziel
Comment Utility
manf788265's solution is the best I've seen yet.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 4

Author Comment

by:sconnell
Comment Utility
murphey2: Thank you for your input (and everyone else as well) but I cannot use a macro function.  Problem is that until there is a fool-proof way to determine whether or not a macro is benign in nature, I will strip macros from all Microsoft products (using MIMESWEEPER).  

This is my policy because until MS can get their brains in gear and strip down their 'powerful' Office VB language so that it is immune to macro virus's.

As far as using the Toolpak, another policy I have is that everyone must use standard tools... no add-ons because it just causes troubles when one cannot guarantee that a file or spreadsheet can be used by everyone without modification.

I have these policies in place for a reason, to keep calls to support to a minimum.
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
The Analysis ToolPak is on the Office CD, this is no homebrew add-in. Do you rule out Solver and other add-ons too ?

Is the solution I sketched in my last post acceptable to you ?
0
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
Frankly, sconnell, your original question never stated that you could not use VBA or an addin. The addin is an integral part of Excel.

Macro viruses are very easy to remove, so I don't understand the necessity not to use visual basic for apps. Not only that, but I'd like to think you keep your virus dat files up to date.

I've already answered your original question.
0
 
LVL 4

Author Comment

by:sconnell
Comment Utility
Dreamboat,
I never stated those requirements because I did not realize they were necessary to make.  I have tried your suggestion and it does work.  It was simply a matter of click a checkbox in the available tools section.  The problem is that I cannot guarantee that all 200 clients will have this capability.  That is why I do not wish to use anything not available as a default.  85% of my clients do not share the same technical ability as I do.

Yes, you did answer my question and so did several others. All were correct, however, you must agree that the solution must work in my environment.  

In regards to macros.  There is not a single virus scanner in the world that can 100% protect from every malicious macro out there.  Updates are irrelevant.  My solution was simple, eliminate macros altogether.  I haven't had a single macro virus problem since I instituted that extreme policy.

Anyhow, unless no other (non-vb, non-analysis pack) alternative is suggested, I will use Dreamboat's suggestion.

Thank you to everyone
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
sconnell, I sketched a "..(non-vb, non-analysis pack)..) twice w/o even getting a feedback. Are you sore because I first posted a solution needing the Toolpak ?
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
SO you like to solve your problem,
-without installing the toolpack (dreamboat)
-without a complicted formula (cri)
-without a macro function (murphey2)

On this moment I'm working at a site that has a very high level of security in there validated systems with a lot of audits from the FDA etc. and using macro's are a normal procedure.
To defend maco-virus attacs we using McAfee and we have no problems with virusus the last 8 years (on non of the 1853 workstations that are installed in our European WAN.
The only 2 thing that make oup PC's crach (sometimes) is Windows 95 and inernet downloads that are the only things that are realy hard to get control of.

So if you that afraid for virusus, you better also remove internet connection and Windows from your PC.



 

Maybe you better do it also without excel and use Lotus123 instead (no macro-virus risk)...


0
 
LVL 13

Expert Comment

by:cri
Comment Utility
And ?
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
Yes tell us
0
 
LVL 1

Expert Comment

by:forenzixbe
Comment Utility
get all your dates in a column (on a seperate spreadsheet, hidden)
select them all and go to Edit - (an option I can't translate 'cuz I'm using the Dutch version ...) - Range

Then in the options that appear, you can select Weekday.

In your working spreadsheet you can now compare the date with the range on the hidden spreadsheet, and if it is not in the range, it is not a working day.

Thank you ..... so much ....

Take Care,

F

Oh yea, I locked this question because I also proposed an answer in your question 'Automatic Password Change' that you no longer seem interested in ... Sorry for that :)
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
forenzixbe,

Next time you do this, please put a big comment ***** TRYING TO GET ATTENTION OF QUESTION ASKER, TENDS TO FORGET QUESTIONS ***** at the start of it. It might diminue the chances of getting other experts angry ;-) (as I originlly was before I read your last line).

Or post a complaint in Community Support and ask a moderator to give feedback.

I will call a moderator right now to:

1- Reject your answer here (I know that's not a problem for you, see no. 2)

2- Investigate sconnell's question history, and take action.

Regards
calacuccia
0
 

Expert Comment

by:costello
Comment Utility
Lead here by calacuccia.

I think that this question should be awarded to DreamBoat.

sconnell, I'll give you 10 days to reply to my post, after that, I'll award points for this question to Dreamboat (kind of postume award).

forenzixbe,
I'm looking at your other question right now.

costello
Community Support Moderator @ Experts-Exchange
0
 

Expert Comment

by:costello
Comment Utility
Forenzixbe,

You are overreacting a bit.

In the other question (http://www.experts-exchange.com/jsp/qShow.jsp?ta=winnt&qid=20142009), your first contribution was rejected by sconnel with a very good reason "forenzixbe: thanks but please don't answer a question unless you're positive that your answer is absolutely
correct.  The 'password' never expires workstation setting is irrelevant because the pw is not expiring
at the workstation, rather at the server.".

Your second post was made 14 minutes before answering this question. This is not a way to alert question askers. Sconnell should have resonded already in THIS question, and also in the other question, but please do not use this 'Answer' method anymore.

Sconnell,

Since the warning by computer101 in this question (http://www.experts-exchange.com/jsp/qShow.jsp?ta=winnt&qid=20095125) and other questions, you seem to have made some progress.
Please furfill this progress on ALL your question.

As stated above, I'll give you about 10 days to react.

costello
Community Support Moderator @ Experts-Exchange

0
 
LVL 1

Expert Comment

by:forenzixbe
Comment Utility
-some comment :

- the rejected answer was one my first post as far as I can remember, I accidentally used Answer instead of comment.  The reason he gave was not good, since everybody knows that Password Never Expires option is located in UserManager For Domains and not on the Workstation. It still is the best solution for a Workstation that logs on automatically ANYWAY !

- I was not trying to get attention for me (my answer was an extension of somebody else's). But there were some people before me that were asking for his reaction.

- On 28/6 sconnell stated "Now that realise that this is probably beyond the scope of EE!", why didn't he delete the question at that time?

- Use Schmiegu on that question probably invested some time in writing the script he proposes, I just extended his script a little.  Schmiegu needs to be awarded the points he deserves.  I need nothing (allthough my solution still is the least work-intensive =) AND everlasting)

- Out of respect of other experts people should follow up their questions and close them if they don't need them anymore.  Disregarding a question proves a lack of respect for other Experts !

Costello >> I will contact you next time I find a 'dead question' instead of locking it =)
0
 

Expert Comment

by:costello
Comment Utility
forenzixbe,

Don't understand me wrong, I do agree with your action in most perspectives. It is extremely rude to let questions where lots of experts have put time into gets abandonned. I did not realize that you were doing this out of general interest, I swa this question with an answer, and the other one you answered, so maybe I jumped to conclusions too fast. Take back what I said about 'Overreacting'.

sconnell,

You can read, you've got notifications, up to you now.

costello
Community Support Moderator
0
 

Expert Comment

by:costello
Comment Utility
We are approaching deadlines, sconnell. YOu have 4 more days to react.
0
 

Expert Comment

by:costello
Comment Utility
Deadline calling me in here...

I am force accepting DreamBoat's contribution.

sconnell, you have more that 1600 question points outstanding in open questions. I will be sweeping through them right now.

costello
Community Support Moderator @ Experts-Exchange
0
 
LVL 4

Author Comment

by:sconnell
Comment Utility
Okay please people... What's all the fuss about?  I ask a lot of questions, sometimes the responses are overwhelming and it takes me time to read all of them (sometimes I miss responses) between my job (which involves much travel).  Look, to imply that I'm rude is truly unjustified!  I've been very fair with awarding points over the past two years (or so).
Remember I'm paying cash for the points I generously give away!  
True, it's a heck of a lot cheaper than hiring a local consultant!

I'm not sure what happened with this question because I'm positive I had already award points to Dreamboat!  It was that solution which worked out quite well as the analysis pack is simply a 'toggle' from each workstation copy of Excel.  

Cri...you wrote 'Is the solution I sketched in my last post acceptable to you ?'  but where is it?  Do you mean the web site ref?  Then no it wasn't.
I would have preferred a more mathematical solution, however, with my increasing workload I find that simple/quick solutions save me from a lot of stress.

Thanks again to everyone!  And yes, I do appreciate everyone's contribution.  Look at my history, I used to contribute a lot myself... now... I have so little time and more questions than answers!  
0
 

Expert Comment

by:costello
Comment Utility
Thanks for coming back to your question sconnell, and I did notice that your are also contributing as an expert. Still, although I have no problem with questions not being solved within the first 2/3 days, it is not good to keep questions which are not active anymore open for longer that 20 days. After such period of inactivity they should either be deleted or the points should be awarded, or maybe require new input. My job also implies travelling often, and takes a lot of time, so that's only a half valid excuse.

I don't want to discourage you to use the EE service, not at all, I wanted to point out that you need to try to remain responsive towards the experts who spend time to try to help you.

Regards
costello
0
 
LVL 1

Expert Comment

by:forenzixbe
Comment Utility
case closed, everybody happy, next question .... please.
:-)
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
sconnell, see my post of 06/27/2001 02:33PM PST, I clearly sketched a solution. If there is one thing that irritates most experts if when the asking person does not take the time to read and give feedback to the contributions.
0
 
LVL 4

Author Comment

by:sconnell
Comment Utility
cri,
You only eluded to a question you previously answered.  
I might have awarded the points to you had you included a link to your answer to "Work around NETWORKDAYS required". Yes, I found it.... I just happened to have some time today to search for your solution.

It is a very good solution, unfortunately, I've made a simple 'patch' for clients to automatically activate the toolpak.  Networkdays works just fine.

Though it appears I was ignoring you... that is hardly the case.  I appreciate and value your input as always!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now