Solved

# Excel - Find number of 'work' days between dates

Posted on 2001-06-27
360 Views
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
Question by:sconnell
• 8
• 8
• 6
• +7

LVL 22

Accepted Solution

Dreamboat earned 50 total points

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Not sure whether the Toolpak is in the standard setup.
0

LVL 1

Expert Comment

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()
End Sub

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

Regards, Manfred
0

LVL 16

Expert Comment

======================
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

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

manf788265's solution is the best I've seen yet.
0

LVL 4

Author Comment

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

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

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.

0

LVL 4

Author Comment

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

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

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

And ?
0

LVL 16

Expert Comment

Yes tell us
0

LVL 1

Expert Comment

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

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

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

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.".

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.

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

costello
Community Support Moderator @ Experts-Exchange

0

LVL 1

Expert Comment

-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

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,

costello
Community Support Moderator
0

Expert Comment

We are approaching deadlines, sconnell. YOu have 4 more days to react.
0

Expert Comment

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

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

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

case closed, everybody happy, next question .... please.
:-)
0

LVL 13

Expert Comment

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

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

### Suggested Solutions

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â€¦