dont count saturdays or sundays

[scottie1]

Hi Peeps

I have a spread sheet and say where column a has a date in it and column b
has a date in column c will calculate the number of days between the two
dates. Is there any way it can count the number of days not inculding
saturdays and sundays

 

[Chance1234]

Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
Dim d As Long, dCount As Long
    For d = StartDate To EndDate
        If WeekDay(d, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next d
    GetWorkDays = dCount
End Function

Thats the VBA solution but, just discovered this myself

there is a function called Networkdays which will also do the same already in excel (where did that come from ?)

Filmmaker, gentleman and _ _ _ _ _ _ _ _

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[scottie1]

Hi There


that does the trick

cheers

i dont suppose we can get it running of a command button and for the info to go in selected cell

 

[xlbo]

Just use the NETWORKDAYS function that Chance has just discovered - all you need to do is activate the Analysis Toolpak add-in - Tools>Add-Ins>Tick Analysis Toolpak

That way it will recalc as all standard functions do

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Chance1234]

not sure what you are asking there scottie
can you rephrase?


Filmmaker, gentleman and _ _ _ _ _ _ _ _

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[scottie1]

Oh yeh

Doh that makes life a little easier

 

[strongm]

>where did that come from?


Excel also has a function called WORKDAY, which returns a number that represents a date that is the indicated number of working days before or after a date. Once you know this, then it is only a small step to understand the existence of NETWORKDAYS function that is actually dealing with NET WORKDAYs rather than some NETWORK DAYS...(which is where I supect the confusion is coming from)

 

[Chance1234]

just never noticed it before,



Filmmaker, gentleman and _ _ _ _ _ _ _ _

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[ajcarr]

NETWORKDAYS used to have some problems. I used to write code which had to be used in many countries but since this routine is in an add-in, it is not resolved when loading in languages other than english - the local language equivalent has to be used. Built-in routines have an English to Local Language lookup which is not the case with add-ins.

I don't know what the latest version does but in Excel '97, there were both VBA and Excel routines which gave different answers (both wrong!) when trying to cope with public holidays which fall on weekend days, which happens quite often on the Continent with fixed date holidays.

I gave up on NETWORKDAYS and wrote my own routine!

Allan

 

[VBAjedi]

Allan,

Are you going to share?

VBAjedi

 

[ajcarr]

Unfortunately, I'm not sure that I still have it. I no longer work for the company where I was when I wrote it. I'll have to look through some of my old backups.

Allan