Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
I want to create a test schedule. My knowns are the start_date and number of
days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
Can you get the analysis toolpak? Cause workday is what you want for this.
Without that, seems like a lot of code. "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
Hi,
Try this as an alternative Start date in a1 Duration of task in A2 =A1+A2+CHOOSE(WEEKDAY(A1+A2),1,0,0,0,0,0,-1) Mike "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
See this for alternate formulas for the ATP functions:
http://www.dailydoseofexcel.com/arch...-addin-part-1/ You'll notice that some of them are very complex which is a good reason to install the ATP! -- Biff Microsoft Excel MVP "Rae" wrote in message ... I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
What if A1+A2 = a holiday?
-- Biff Microsoft Excel MVP "Mike H" wrote in message ... Hi, Try this as an alternative Start date in a1 Duration of task in A2 =A1+A2+CHOOSE(WEEKDAY(A1+A2),1,0,0,0,0,0,-1) Mike "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
hi,
Thanks for that. I agree my answer is simplistic but didn't intend to consider holidays. The OP didn't give a start date of a weekend that sets of the chain of testing periods my formula returns the weekday I thought was being asked for. Mike "T. Valko" wrote: What if A1+A2 = a holiday? -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Hi, Try this as an alternative Start date in a1 Duration of task in A2 =A1+A2+CHOOSE(WEEKDAY(A1+A2),1,0,0,0,0,0,-1) Mike "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
Hi,
Assuming your Start date is in A1 and the number of business days in B1, and any company holidays are listed in G1:G4, then this is your formula: =WORKDAY(A1,B1,G$1:G$4) In 2003 you need to attach the Analysis ToolPak - Choose Tools, Add-ins and check Analysis ToolPak In 2007 nothing to do, this function is built into Excel. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the end date?
You may have missed this:
do not have the analysis toolpak that will allow me to use workday or edate -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Assuming your Start date is in A1 and the number of business days in B1, and any company holidays are listed in G1:G4, then this is your formula: =WORKDAY(A1,B1,G$1:G$4) In 2003 you need to attach the Analysis ToolPak - Choose Tools, Add-ins and check Analysis ToolPak In 2007 nothing to do, this function is built into Excel. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rae" wrote: I want to create a test schedule. My knowns are the start_date and number of days for each tester. So when I plug in the start_date it will automatically adjust the start and end dates for each tester. Tester 1 starts testing on 12/08/2008 and it will take him 3 business days to test; tester 1 should be done testing by 12/11/2008 (calculated based on start_date and number of business days). Tester 2 can't start testing until Tester 1 has completed testing, so if tester 1 is done by 12/11/2008 (this will be the start date for tester 2) and tester 2 needs 15 business days to test. Tester 2 should be done testing by 12/31/2008 (calculated end date also excluding holidays). And so forth.... I have 2 additional testers where the start date and end date needs to be calculated based off of the original start date and the number of business days. Thank you, Rae (do not have the analysis toolpak that will allow me to use workday or edate, but I don't think edate will work in this case since I am calculating end dates) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating using a date | Excel Discussion (Misc queries) | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Calculating due date? | Excel Worksheet Functions |