I hate date math!
 

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

I hate date math!
 

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.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



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

I hate date math!
 

On Thu, 12 Jan 2006 18:23:08 -0700, Steve Roberts wrote in
microsoft.public.outlook.program_vba:

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.

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.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"