# Creating a project kick off date based on go live dates

Options

I have a project tracker where I'd like to be able to predict project kick off dates based on when projects go live. It ideally would find the minimum FPFV (Updated) date and calculate the project kick off date as the max actual go live date + 1 week. Any thoughts on how best to do this?

• ✭✭✭✭✭
Options

Joe, assuming you want to use the maximum of the entire column, try this formula in the Actual Go Live Date column:

=MAX([Projected Project Kickoff Date]:[Projected Project Kickoff Date])+ 7

I hope that works for you.

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• Options

Thanks Ramzi. I do want to use the max for the actual go live date and have used that formula but it is a bit more complicated than that. Our priority says we should use the next (or min) FPFV (updated) which is also a date column. I have the following formula:

= IF(MIN([FPFV (Updated)]1:[FPFV (Updated)]107)), (Max([Actual Expected Go Live Date]1:[Actual Expected Go Live Date]106)) + 7, "error")

• edited 10/05/20
Options

Any thoughts on this? Am I able to use Min/Max function within an If statement? I have looked into Max(Collect()) but that doesn't seem to be useful because I can't use the Max function as the criterion unless I am doing it incorrectly.

• Employee
Options

Yes! You can use MIN/MAX functions inside IF statements, however your current formula isn't telling the IF statement what to do... you tell it to first find the MIN in a certain column, but then there's no instruction once it's found that data.

Try something like this:

=IF(MIN([FPFV (Updated)]1:[FPFV (Updated)]107) > Max([Actual Expected Go Live Date]1:[Actual Expected Go Live Date]106), MIN([FPFV (Updated)]1:[FPFV (Updated)]107), (Max([Actual Expected Go Live Date]1:[Actual Expected Go Live Date]106)) + 7)

This says, if the MIN in the FPFV (Updated) column is greater than the MAX in the Actual Expected Go Live Date column, then return the MIN of the FPFV (Updated) column.

Otherwise, return the MAX of the FPFV (Updated) column.

Is that what you were looking to do?

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

Genevieve, thank you. I actually realized that and updated my formula to set the min range if = to the FPFV (Updated)@row to the max actual expected go live date + 7 days. The issue I am running into now is the min range keeps being altered when filtering is applied. Any experience with correcting that?

• Employee
Options

Since your range is using row references (ex. row 1.- 107) then the data in those rows will update if the sheet is sorted and other data appears within rows 1 - 107.

If possible, I would suggest using an entire column as a reference instead of specifying a row start and a row end, ex: Max([Actual Expected Go Live Date]:[Actual Expected Go Live Date]), but this would depend on your sheet set up.