Sub item column value on parent

Hello Members,

When the status of the parent item is “In Progress”, the Task - Completed Date column should show Today, and when the status is “Done” I need the the latest date from the sub item column “Completed Date”.

The purpose to do this is to calculate the duration of the task. The show summary on parent isn’t helping.

Another question, is it even possible to use the sub item date column and get the latest value on a parent formula column.