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?


Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    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

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Joe Haney
    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")

  • Joe Haney
    Joe Haney ✭✭
    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.

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Joe Haney
    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?

  • Genevieve P.
    Options

    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.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Joe Haney
    Joe Haney ✭✭
    edited 10/07/20
    Options

    Thank you Genevieve - that worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!