End Date Calculation (adding a start date duration)

[Silena K-K]

Hi there

I want to generate an End date by adding together a start date and a
duration - one of 30 days (including weekends and holidays) and one of 30
working days (business days only)

Eg: if my start date is 30 Jun 08 what is the finish date after 30 working
days
if my start date is 30 Jun 08 what is the finish date after 30 days

Thanks for your help, Silena

 

[Dave Peterson]

=startdate+30
(and format it as date)
=a1+30

And look up =workday() in xl's help.

If you're running xl2003 or below, you'll have to have the analysis toolpak
loaded. Help explains this.

 

[JLatham]

My first question: whose holidays? United States, Brazil, Canada? Deciding
what's a weekend day is fairly easily, but holidays can toss in a curve -
especially when you have situation like in the U.S. where if a federal
holiday falls on a Saturday, it's observed on Friday and if it falls on
Sunday, then it's observed on Monday.

I've actually got stuff available now to determine when U.S. Federal
holidays are observed that could be worked into a solution for your
situation, but for holidays elsewhere, it would no doubt take some
modification. Individual additional holidays, such as those observed by the
various states in the U.S. would also have to be given consideration.

 

[JLatham]

Another option, in addition to Dave's that may save you having to create a
list of holidays for the WORKDAY() function. But if you're using non-U.S.
holidays, his suggestion is the way to go.

You'll need/want 2 workbooks that I've uploaded:
http://www.jlathamsite.com/uploads/Plus30WorkingDays.xls
that has example sheet with code in it.

And if you're not too much for copying/pasting code or working with the VBA
Editor to get that done, you can download this file that I've set up just for
you:
http://www.jlathamsite.com/uploads/CopyModulesBetweenBooks_4Selina.xls
(yeah, I spelled Silena wrong, oh well - shoot me). Follow the instructions
in it to have it copy all the necessary code from the Plus30... workbook into
your own workbook and have it all ready for use. They may look complex, but
they're really not, just went into detail to make sure people got it all
right.

Look at the formula in column C of the Plus30WorkingDays.xls workbook to see
how to write your own in your workbook to use the User Defined Function (UDF)
that's going to end up being available to you.

 

[Silena K-K]

Hi Dave & JLatham

Thanks for your suggestion Dave.

For JLatham - I'm in New Zealand. As far as I am aware most of the
'state/public' holidays are on a set day not date excluding Christmas and New
Year. If either the 25th or 26th Dec / 1st or 2nd Jan fall on a weekend we
get the next two business days off.

Any suggestions? Thanks again, Silena

 

[JLatham]

Then I'd probably recommend going with Dave Peterson's solution. The
Workdays() function allows you to set up a list of dates in your workbook
that are to be considered non-workdays (i.e. holidays) along with Saturdays
and Sundays. You'd just have to build that list.

The other option would be if you can give me a list of all holidays to be
considered and the rule(s) that go with them, or point me to a NZ website
that gives that kind of information, I could probably modify the holiday
finding code to work with NZ holidays. If you want to try to go that way, my
email is (remove spaces):
HelpFrom @ JLathamsite.com