Need Help not counting weekends

[ribhead]

I am trying to calculate date differences for vacations and I don't want to include Saturday or Sunday. I wrote some code but it doesn't work (obviously). I did a keyword search and found nothing for Excel. I thought there was a Function in Excel that would do this but I guess not? Any help would be great.

Sub dude()
Dim newnum As Range
Dim startday As Date
Dim lastday As Date
Dim count As Integer
count = 0
startday = Cells(1, 1)
lastday = Cells(1, 2)
mydays = DateDiff("d", startday, lastday)
Set newnum = mydays
For Each d In newnum
If Weekday(d) <> 1 Or Weekday(d) <> 7 Then
count = count + 1
End If
Next d
Cells(4, 1) = count
End Sub


I may not be very smart but I'm sure wirey!!!!

 

[henio]

You need the NETWORKDAYS function.

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

Regards,
Henio

 

[TopJack]

NETWORKDAYS function mentioned by henio is a good solution. If this function is not available then you can use other functions to get the same results. I found this good website by Chip Pearson (on Excel) that goes into great detail about this problem.

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

Hope this helps,
TopJack.

 

[ribhead]

Can I put a list of holidays in a column? I can't get this function to work unless I put quotes around dates. I want to use textboxes or even ranges but when I use these in the formula I get error in my formula. How can I define multiple holidays in this formula using Ranges?

Here is my formula

=NETWORKDAYS(STARTDATE,ENDDATE,{myd})

I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

Well I got this to work as an Excel Function but I'm not sure why this Function doesn't show up in my VBA code.

I tried writing
Cells(1,1)=Application.WorksheetFunction.NETWORKDAYS(STARTDATE,ENDDATE,HOLIDAYS)

This doesn't work If I insert this in to a cell it works just fine.

Hmmmm.

Any help or suggestions would be nice.

I may not be very smart but I'm sure wirey!!!!

 

[xlbo]

Not all worksheet functions are available in VBA - simple as that

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[chandlm]

Ribhead,

I have played around with your original macro and have managed to get it to work.

I have tested it against the excel networkdays function and it returns the same value everytime.


Sub dude()
Dim newnum As Integer
Dim startday
Dim lastday
Dim count As Integer
startday = Cells(1, 1).Value2
lastday = Cells(1, 2).Value2

count = 0

For a = startday To lastday
wkday = Format(a, &quot;dddd&quot
Select Case wkday
Case &quot;Saturday&quot;
Case &quot;Sunday&quot;
Case Else
count = count + 1
End Select

Next a

Cells(4, 1).Value = count
End Sub


Hope this is something like what you wanted.

Matt

 

[ribhead]

chandlm , Thanks for taking the time. I did get the Networkdays to work except I couldn't use the WorksheetFunction with it I had to insert the formula into the cell. A big thanks to everyone who helped me on this!!!

Beep Beep.

I may not be very smart but I'm sure wirey!!!!

 

[lucrahouse]

I see a lot of good stuff on this idea but I did it my way.
I threw in a calendar of holidays, too. I checked to make sure it works - you can make it a function if you want. Enjoy!

Sub dude()
Dim newnum As Range
Dim startday As Date
Dim lastday As Date
Dim count As Integer
Dim dateItem(9) As Date

' Holiday Calendar
dateItem(1) = &quot;1/1/2003&quot; ' NEW YEARS DAY
dateItem(2) = &quot;1/20/2003&quot; ' MARTIN LUTHER KING, JR. DAY
dateItem(3) = &quot;2/17/2003&quot; ' PRESIDENT'S DAY
dateItem(4) = &quot;4/18/2003&quot; ' GOOD FRIDAY
dateItem(5) = &quot;5/26/2003&quot; ' MEMORIAL DAY
dateItem(6) = &quot;7/4/2003&quot; ' INDEPENDENCE DAY
dateItem(7) = &quot;9/1/2003&quot; ' LABOR DAY
dateItem(8) = &quot;11/27/2003&quot; ' THANKSGIVING DAY
dateItem(9) = &quot;12/25/2003&quot; ' CHRISTMAS DAY

count = 0
startday = Cells(1, 1)
lastday = Cells(1, 2)
MyDays = DateDiff(&quot;d&quot;, startday, lastday)
CurrentDay = startday

If lastday > dateItem(9) Or startday < dateItem(1) Then
MyMsg = MsgBox(&quot;It's time to update the holiday calendar in the LookUpDate function&quot
Exit Sub
End If

For x = 1 To MyDays
CurrentDay = startday + x
If Weekday(CurrentDay, 2) < 6 Then
For i = 1 To 9
If CurrentDay = dateItem(i) Then
count = count - 1
End If
Next i
count = count + 1
End If
Next x
Cells(1, 3) = count
End Sub