Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
=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. Silena K-K wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
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. "Silena K-K" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
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/P...orkingDays.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/C...ks_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. "JLatham" wrote: 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. "Silena K-K" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
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" wrote: 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. "Silena K-K" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
End Date Calculation (adding a start date duration)
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 "Silena K-K" wrote: 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" wrote: 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. "Silena K-K" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Probablility calculation related to start date | Excel Discussion (Misc queries) | |||
Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel |