Excel - Find number of &#x27;work&#x27; days between dates

ASKER

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

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

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

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

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

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

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

blakeh1

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

ASKER

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?

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.

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.

Anne Troy

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

Not sure whether the Toolpak is in the standard setup.

manf788265

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

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

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.

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

ASKER

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.

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 ?

Anne Troy

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.

ASKER

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

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 ?

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

And ?

Yes tell us

forenzixbe

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 :)

calacuccia

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

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

Forenzixbe,

You are overreacting a bit.

In the other question (https://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 (https://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

forenzixbe

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

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

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

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

ASKER

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!

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

forenzixbe

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

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.