date problem

[loki]

I want to find out how many days are between two dates but I don't want to count the weekends. I know how to use DateDiff but I don't know how to test for Saturaday and Sunday and leave them out of my calculation. Thanks

 

[Robert Saye]

Try the NETWORKDAYS:

NETWORKDAYS
Returns the number of whole working days between a start and end date, excluding weekends and any identified holidays.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

Holidays is an optional range of one or more days (holidays) to exclude from the working calendar.

(Copied from A2K Help File)

 

[D-Fresh]

or you could use a function for it... Try this...

The function takes in a start date and an optional end date.. if the end date is missing, then it will default to current day..

Public Function NumDays(StartDate As Date, Optional EndDate) As Integer

NumDays = 0
If IsMissing(EndDate) Then
If StartDate <= Date Then
While StartDate <> Date
If WeekDay(StartDate) <> vbSaturday And WeekDay(StartDate) <> vbSunday Then
NumDays = NumDays + 1
End If
StartDate = StartDate + 1
Wend
End If
Else
If Not IsNull(EndDate) And Not IsNull(StartDate) And EndDate >= StartDate Then
While StartDate <> EndDate
If WeekDay(StartDate) <> vbSaturday And WeekDay(StartDate) <> vbSunday Then
NumDays = NumDays + 1
End If
StartDate = StartDate + 1
Wend
End If
End If

End Function

Hope this helps...

Doug