Yodelayheewho
Member
Hello!
You may want to grab a cup of coffee
I am managing a course creation project. Each task has to be completed by a specific number of workdays until we launch/announce to our customers.
For example:
1. Subject Matter Expert (SME) submits blueprint (due 36 workdays until launch)
2. Approvals round 1 (R1) (due 31 workdays until launch)
3. Module creation (due 11 workdays until launch)
4. Approvals round 2 (R2) (due 6 workdays until launch)
5. Upload (due 4 workdays until launch)
6. GO LIVE (due 2 workdays until launch)
Target launch date (English) (due 0 workdays = launch day)
We have to fully develop the English version before we can develop the Spanish version. So, when developing the Spanish, we 'add' workdays from the English launch day to come up with the due dates.
7. Module translation to Spanish (due 20 workdays post launch English)
8. Upload Spanish (due 22 workdays post launch English)
9. GO LIVE Spanish (due 24 workdays post launch English)
Target launch date (Spanish) (due 26 workdays post launch English)
As what often happens, tasks are not completed on time and I want to track how behind we are.
In column D, I have the Target Launch Dates for English.
Column E mirrors Column AT.
My big hairy challenge is how do I calculate the Trending Date in AT. (Once I figure that out I'll be about to get BL too).
I figure I need to take last non blank Actual Date (Columns P,U,Z,AE,AJ,AO) then add the remaining workdays until launch.
For example, in row 3, the last non blank is in Column U3. It shows the date: 12-23-19.
I tried this formula: =WORKDAY.INTL(U3,(11+6+4+2+2),1,HOLIDAYS)
It works in theory. Now, I need to figure out:
1. how do I find the 'start-date' in the formula, which needs to be the last non blank between Columns Columns P,U,Z,AE,AJ,AO?
2. how do I find the number of days, which needs to be found by adding the workdays for each task +2 to the right of the start date?
Thank you in advance for your willingness to help!
You may want to grab a cup of coffee
I am managing a course creation project. Each task has to be completed by a specific number of workdays until we launch/announce to our customers.
For example:
1. Subject Matter Expert (SME) submits blueprint (due 36 workdays until launch)
2. Approvals round 1 (R1) (due 31 workdays until launch)
3. Module creation (due 11 workdays until launch)
4. Approvals round 2 (R2) (due 6 workdays until launch)
5. Upload (due 4 workdays until launch)
6. GO LIVE (due 2 workdays until launch)
Target launch date (English) (due 0 workdays = launch day)
We have to fully develop the English version before we can develop the Spanish version. So, when developing the Spanish, we 'add' workdays from the English launch day to come up with the due dates.
7. Module translation to Spanish (due 20 workdays post launch English)
8. Upload Spanish (due 22 workdays post launch English)
9. GO LIVE Spanish (due 24 workdays post launch English)
Target launch date (Spanish) (due 26 workdays post launch English)
As what often happens, tasks are not completed on time and I want to track how behind we are.
In column D, I have the Target Launch Dates for English.
Column E mirrors Column AT.
My big hairy challenge is how do I calculate the Trending Date in AT. (Once I figure that out I'll be about to get BL too).
I figure I need to take last non blank Actual Date (Columns P,U,Z,AE,AJ,AO) then add the remaining workdays until launch.
For example, in row 3, the last non blank is in Column U3. It shows the date: 12-23-19.
I tried this formula: =WORKDAY.INTL(U3,(11+6+4+2+2),1,HOLIDAYS)
It works in theory. Now, I need to figure out:
1. how do I find the 'start-date' in the formula, which needs to be the last non blank between Columns Columns P,U,Z,AE,AJ,AO?
2. how do I find the number of days, which needs to be found by adding the workdays for each task +2 to the right of the start date?
Thank you in advance for your willingness to help!
