Scheduled Dates

Jonathan Beaty

I am a Project Manager working on a project plan for a technology company. I estimate dates based on the initial project kick-off date. I allow a certain amount of days for each process step.

 

Example:

 

- Kick-Off Date = 01/01/2017

- Estimated Development Start Date =[Kick-Off Date] + 7 = 01/08/2017

- Estimated Development Completion Date =[Kick-Off Date] + 56 = 02/19/2017

- Estimated Testing Start Date =[Kick-Off Date] + 63 = 02/26/2017

- Estimated Testing Completion Date =[Kick-Off Date] + 70 = 03/04/2016

 

The next step in the development process is to attend a Change Advisory Board meeting to be granted their blessing to migrate configuration into Production environment. Once I know the applicable CAB meeting date, I can then estimate the date for migration and estimated date of user-acceptance in the Production environment, which in turn, tells me the estimated date for project completion, and therefore, invoicing date.

 

My issue is that the CAB only meets every other Tuesday. If I know the first CAB meeting of the year is Tuesday, 01/05/2017, then I should be able to determine all CAB meeting dates for the entire year (CAB1, CAB2, CAB3) : (01/05/2017, 01/19/2017, 02/02/2017).

 

I am looking for a formula or way to project the date on which the project resource will be able to attend the next CAB meeting. There is scheduling involved to get on the list, so the next available meeting time would not be the next CAB meeting following [Estimated Testing Completion Date], but the CAB meeting after that.

I assume there is a way to do it via look-up tables? Any advice or direction will be much appreciated!

 

J. Craig Williams

Jonathan,

 

Have you already listed all of the CAB dates for 2017 or will they be calculated dynamically?

 

And regardless whether the Estimated Testing Completion Date  is on the day after the last CAB or the day before the next one, there is always one and only one CAB skipped?

 

BTW, The first Tuesday this year was on the 3rd, not the 5th and your date for the Estimated Testing Completion Date is wrong - it should be 2017.

 

Back later. I have an idea.

Most important question for that idea - what happens for the two cases when the ETCD is on a Tuesday?

 

Craig

 

 

J. Craig Williams

All you need it the first CAB date.

I put mine in a column name [CAB Date] in row #1

 

Here's the formula in row 23 to determine the next CAB date. May need slight tweaking depending on your answer to my questions.

My assumption is you always skip one meeting.

I have the IF block worked out if there is a difference between week 1 and week 2.

 

=[CAB Date]$1 + (INT((Date23 - [CAB Date]$1) / 14) + 2) * 14

 

This just uses the MODULUS to get the next meeting date.

 

Craig