I hate date math!

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
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
 
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.
 
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.
 
Back
Top