If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Calculation problem
Hello,
I have a work sheet with "JIRA" priority levels and dates the JIRA was created. Each priority leve allows a certain number of days to be complete the JIRA. I need to be able to figure out if we resolved the JIRA within the allotted number of days, excluding: 1. weekends 2. holidays I have tried to use the NETWORKDAYS() function, but I cannot use this function because I do not know the due date of the JIRA. I used the weekday function to add days for weekends and come up with the actual due date....but this does not take holidays into account. My data basically looks like: A B C Priority date entered Date Completed P - 1 1/1/10 1/5/10 P - 2 1/2/10 1/6/10 P - 3 1/3/10 1/7/10 P - 4 1/4/10 1/8/10 I also have a table of holidays. This table is a range named "holidays" A B New Years Day 1/1/2010 Martin Luther day 1/18/2010 memorial day 5/31/2010 How can I come up with the due date for the JIRAs, based on the entry date and take into account priority, weekends and holidays? Thanks, Pete |
#2
|
|||
|
|||
Date Calculation problem
Use the Workday function, rather than Weekday. It supports holidays as
required. Regards, Fred "petedacook" wrote in message ... Hello, I have a work sheet with "JIRA" priority levels and dates the JIRA was created. Each priority leve allows a certain number of days to be complete the JIRA. I need to be able to figure out if we resolved the JIRA within the allotted number of days, excluding: 1. weekends 2. holidays I have tried to use the NETWORKDAYS() function, but I cannot use this function because I do not know the due date of the JIRA. I used the weekday function to add days for weekends and come up with the actual due date....but this does not take holidays into account. My data basically looks like: A B C Priority date entered Date Completed P - 1 1/1/10 1/5/10 P - 2 1/2/10 1/6/10 P - 3 1/3/10 1/7/10 P - 4 1/4/10 1/8/10 I also have a table of holidays. This table is a range named "holidays" A B New Years Day 1/1/2010 Martin Luther day 1/18/2010 memorial day 5/31/2010 How can I come up with the due date for the JIRAs, based on the entry date and take into account priority, weekends and holidays? Thanks, Pete |
#3
|
|||
|
|||
Date Calculation problem
Thanks fred,
I will try this and come back to click the "Yes" button if it does it.....it looks like it will. "Fred Smith" wrote: Use the Workday function, rather than Weekday. It supports holidays as required. Regards, Fred "petedacook" wrote in message ... Hello, I have a work sheet with "JIRA" priority levels and dates the JIRA was created. Each priority leve allows a certain number of days to be complete the JIRA. I need to be able to figure out if we resolved the JIRA within the allotted number of days, excluding: 1. weekends 2. holidays I have tried to use the NETWORKDAYS() function, but I cannot use this function because I do not know the due date of the JIRA. I used the weekday function to add days for weekends and come up with the actual due date....but this does not take holidays into account. My data basically looks like: A B C Priority date entered Date Completed P - 1 1/1/10 1/5/10 P - 2 1/2/10 1/6/10 P - 3 1/3/10 1/7/10 P - 4 1/4/10 1/8/10 I also have a table of holidays. This table is a range named "holidays" A B New Years Day 1/1/2010 Martin Luther day 1/18/2010 memorial day 5/31/2010 How can I come up with the due date for the JIRAs, based on the entry date and take into account priority, weekends and holidays? Thanks, Pete . |
#4
|
|||
|
|||
Date Calculation problem
Thanks, Fred. This worked fine.
"petedacook" wrote: Thanks fred, I will try this and come back to click the "Yes" button if it does it.....it looks like it will. "Fred Smith" wrote: Use the Workday function, rather than Weekday. It supports holidays as required. Regards, Fred "petedacook" wrote in message ... Hello, I have a work sheet with "JIRA" priority levels and dates the JIRA was created. Each priority leve allows a certain number of days to be complete the JIRA. I need to be able to figure out if we resolved the JIRA within the allotted number of days, excluding: 1. weekends 2. holidays I have tried to use the NETWORKDAYS() function, but I cannot use this function because I do not know the due date of the JIRA. I used the weekday function to add days for weekends and come up with the actual due date....but this does not take holidays into account. My data basically looks like: A B C Priority date entered Date Completed P - 1 1/1/10 1/5/10 P - 2 1/2/10 1/6/10 P - 3 1/3/10 1/7/10 P - 4 1/4/10 1/8/10 I also have a table of holidays. This table is a range named "holidays" A B New Years Day 1/1/2010 Martin Luther day 1/18/2010 memorial day 5/31/2010 How can I come up with the due date for the JIRAs, based on the entry date and take into account priority, weekends and holidays? Thanks, Pete . |
Thread Tools | |
Display Modes | |
|
|