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

felixo

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

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.

Michelle Choate 2

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.

SharpC

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()).

felixo

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

Is there anything I can do about it?

Michelle Choate 2

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.

felixo

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