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
-
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.
-
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.
-
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
-
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.
-
Hi Christian,
Please find the attached screenshot of the testing sheet. I am using summary column to arrange parent child relationship.
-
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.
-
Do I need to arrange those unique ID and Parent ID manually in the sheet?
-
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.
-
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
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!