Custom date formula

[Custom Field Formulas]

Hello I am new to ms project and I need to write several formulas that will help determine the submission timeline for several task. Each task follows the same specific timeline, only they start at different times of the year. The below is the timeline guide as well as the order in which the task are tracked.

Inaugural Construction date – ex. 7/1

DAD Submission date – 12 weeks prior to construction

PS&E Submission dat2 – 2-3 weeks after DAD submission

Project approval date – 6 weeks after DAD submission

Project obligation date – 2 weeks after project approval date

FEAW/Etc. – 4 weeks before DAD submission date.

Task ex.

Rockaway Blvd, 89th St to 102nd Rd
– MPT Started 2/15/2017
– FEAW/CEQR Started 3/11/2017
– Target DAD Package Submission Date 4/8/2017
– Actual DAD Submission Date TBD
– Target PS&E Package Submission Date 4/29/2017
– Actual PS&E Submission Date TBD
– Approval Needed 5/20/2017
– Obligation Received 6/4/2017
– Inaugural Construction Started Date 7/1/2017

What I would like to do is to create a formula using the above timeline guide instead of inputting the dates manually. If this is possible any help would be greatly appreciated.

Thank you,
Clint

[Daryl Deffler]

Hey Clint;
Might I suggest that rather than messing with date formulas, you explore relationship with positive or negative lag values. This might be easier as it does not require the use of custom fields and date formulas. Meaning all milestones would show their appropriate start/finish dates using the standard MS Project fields. Additionally, if milestones are used for each, they can also be displayed n the Gantt timeline view to provide a better visual presentation.

So, for example, You could set up milestones for each of the events you noted with 0 work and duration. Then for example, DAD Submission would be a successor or Inaugural Construction but with a lag of -12W (meaning it occurs 12 weeks prior). Project Approval date would be a successor of DAD Submission with a positive lag of 6W (weeks).

If some of these relationships aren’t providing the correct results, you can also try a different twist on the relationships using a task constraint of As Late As possible.
For example, DAD Submission would be a predecessor of Inaugural Construction with positive lag of 12W but the DAD Submission would also contain a task constraint of As Late As Possible. This constraint causes the DAD milestone to be pushed as tight as possible up to the successor task but the 12w lag forces the constraint to honor the 12 week gap.

With these approaches, you may need to add the appropriate constraint to the Inaugural Construction milestone to ensure it occurs on 7/1 in your example. These constraints could be Must Finish or Start On, or Finish/Start On or After, depending on what else you have in the overall schedule.

Lag values can usually be entered where you assign predecessor/successor relationships.

With both these approaches, if the starting point (Inaugural) moves, all related milestones will move also!

Hope that helps

[Clint]

Thank you so much for the great advice Mr. Deffler, I was so focused on needing a formula I never thought about using lag values. I will follow your great advice and let you know how it all turns out.

Much Appreciated,
Clint

[Author]

Posts