How to compare due date of child with parent due date ?

I am trying to find when due date is changed for child if those dates are greater than the parent due date .

PARENT - Due date = 04/12/2022

Child 1 = Due date = 03/29/2022

Child 2 = Due date = 03/30/2022

Child 3 = Due date = 04/12/2022 If a users changes due date for child if that date is greater than the parent then it should show red flag for that row.

Best Answers

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    How is your data arranged? Are the parents and children identifiers in the same column? Is it a list of patients where parents and children are mixed together? If you can share an image of your data that would be better, or copy the sheet and make 4 lines of dummy data.

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    You will need to create a unique ID/ Name Column for each parent then populate a Parent ID column for each child so they are both associated. Something like the image below where you manually populate parent ID and you can have an autonumbered column for the Unique ID.


    then you can make a status column with this formula.

    =IF(COUNTIFS([Parent ID]:[Parent ID], =[Unique ID]@row, [End Date]:[End Date], >[End Date]@row) > 0, "Error, Child Due Date Exceeds Parent Date")

    Then if you want a visual indicator, you can use conditional formatting if status cell is populated, highlight parent date in red.


    Hopefully this helps.

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    If you set up the Unique ID to be an auto numbered list in "edit column properties" then you will only have to populate the Parent IDs for the child rows manually. There is likely a better way to do this, but I've never dealt with parent/child functions.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!