Effective Days function in VBA

[M@N]

Hi all,

I am after a piece of coding to incorporate into a user defined function as follows:

=UDF(start_date , finish_date , [1 or 2 or 3] , 'range of public holiday dates')

1 = calendar days (i.e. finish_date - start_date)
2 = business days (i.e. finish_date - start_date, less weekends and public holidays)
3 = working days (i.e. finish_date - start_date, less Sundays and public holidays)

This is similar to the 'NETWORKDAYS.INTL' standard function, however I cannot use that for two reasons; first the standard 'NETWORKDAYS.INTL' function does not calculate calendar days, and second I need the coding to be part of a larger user defined function that already has a start_date and finish_date field in it.

Any help would be appreciated.

Thanks.

[karedog]

Maybe :

Please Login or Register  to view this content.

[Crooza]

Here's another version

Just check that Karedogs version doesn't count public holidays that also fall on a weekend. These need to be excluded from the count as they overlap.

Please Login or Register  to view this content.

[Crooza]

And here's the file with the UDF

[karedog]

Good catch Crooza, I miss that point.

Fixed UDF :

Please Login or Register  to view this content.

[M@N]

Karedog /Crooza, thank you both. Works a treat!

[Crooza]

Great. Glad it works how you wanted

[karedog]

You are welcome M@N, thanks for the rep. points and marking the thread as solved.


Regards