Creating a project kick off date based on go live dates

Joe Haney

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?

Ramzi K

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

Joe Haney

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")

Joe Haney

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.

Genevieve P.

Hi @Joe Haney

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

Joe Haney

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?

Genevieve P.

Hi @Joe Haney

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.

Joe Haney

Thank you Genevieve - that worked.