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

sam55

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.

Christian Graf

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

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

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.

Christian Graf

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.

sam55

https://community.smartsheet.com/discussion/comment/327768#Comment_327768

Hi Christian,

Please find the attached screenshot of the testing sheet. I am using summary column to arrange parent child relationship.

39BE20C9-D77F-4676-8F32-8A18E82AAFD6.jpeg

Christian Graf

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.

sam55

https://community.smartsheet.com/discussion/comment/327776#Comment_327776

Do I need to arrange those unique ID and Parent ID manually in the sheet?

Christian Graf

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.

sam55

https://community.smartsheet.com/discussion/comment/327785#Comment_327785

Hey Christian, I was able to set those columns using those ID but I am not able to get the status result. please check the attached screenshot. Please let me know if I miss anything in the formula

20769DCD-AB3E-42C8-8C95-5BB32D3A3ABD.jpeg

Christian Graf

You have your formula set to monitor cell 2 of the columns you'll want to have it evaluate every row along the entire column instead.

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