Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    What formula or function can transfer a date from one cell to another?

    ✭
    edited 10/31/24 in Formulas and Functions

    Hi everybody

    I'm looking for a formula to automatically transfer a value (i.e. date) from lower-level cells to a higher-level cell (please see exemplification below).

    Exemplification.jpg

    What formula (or function) can be used for the cells D2 and F2, or D3/F3 and C7/F7 respectively?

    Many thanks in advance for any help.

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      Question, does Row 1 and Row 2 have the formula in them? Because Row 3 appears to be a child of Row 2, which in turn is a child of Row 1. So Row 2 would look at Row 3 to get its dates, then Row 1 would look at row 2. You can see from your format column that Row 3 is a child row. There can never be a 3rd level, without a 2nd level.

      Michelle Choate

      michelle.choate@outlook.com

      Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

      · Share on FacebookShare on Twitter

    Answers

    • ✭
      edited 10/31/24

      For the earliest date, you want to put =MIN(CHILDREN()) in the Start date column of the parent rows, and for the latest date you want =MAX(CHILDREN()) in the End Date column of the parent rows.

      Putting =MIN(CHILDREN()) in D3 will look at D4:D6 child rows and return 10/01/24. In D7 it will look at D8:D10 and return 09/01/24. Putting it in D2 will only look at D3 and D7 and return 09/01/24. Likewise with F column, but using =MAX(CHILDREN()).

      · Share on FacebookShare on Twitter
    • ✭

      Dear SharpC

      Thanks a lot for your rapid answer. The formulas you mentioned are really helpful.
      However, in some cases there is no second level (just first and third level). Unfortunately, in these cases the formulas don't work (see red frame).

      Exemplification2.jpg

      Is there anything I can do about it?

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      Question, does Row 1 and Row 2 have the formula in them? Because Row 3 appears to be a child of Row 2, which in turn is a child of Row 1. So Row 2 would look at Row 3 to get its dates, then Row 1 would look at row 2. You can see from your format column that Row 3 is a child row. There can never be a 3rd level, without a 2nd level.

      Michelle Choate

      michelle.choate@outlook.com

      Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

      · Share on FacebookShare on Twitter
    • ✭

      Thank you very much, Michelle. I could solve the problem!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions