I hate date math!

S

Steve Roberts

I have a custom .oft form for vacations that I am trying to clculate the
number of business days the person will be gone.
This is my current formula that returns the total number of day fine but
does not exclude the weekends.DateDiff("d",[StartDate],[EndDate]) + 1

Can someone point me in the right direction please.

Thanks

Steve
 
M

Michael Bauer

Am Thu, 12 Jan 2006 18:23:08 -0700 schrieb Steve Roberts:

Steve, you can use the WeekDay function. Loop through all days returned by
your function and determine which weekday number it is.
 
M

Michael Bednarek

I have a custom .oft form for vacations that I am trying to clculate the
number of business days the person will be gone.
This is my current formula that returns the total number of day fine but
does not exclude the weekends.DateDiff("d",[StartDate],[EndDate]) + 1

Can someone point me in the right direction please.
Click to expand...

Calculate the absolute week number in VBA's idea of a calendar for both
dates, subtract one from the other, multiply by 2 for weekends, subtract
that from DateDiff.

DateDiff("d", datStart, datEnd) + 1 _
- ((datEnd - Weekday(datEnd, vbMonday) - 1) / 7 _
- (datStart - Weekday(datStart, vbMonday) - 1) / 7) * 2

This doesn't account for public holidays. For that, you could use
Set myExcel = Application.CreateObject("Excel.Application")"
and use Networkdays() in a workbook which contains the holidays.
 
Top