how to compare one parent child value with another parent child value?
first parent child has startdate and end date and second parent child has startdate and enddate, how can i compare both dates are greaterthan or equal or inbetween
Best Answer
-
My suggestion would be a helper column with the following in the child rows:
=PARENT(Infos@row)
Now that we have the material also listed on the child rows, you can use this to flag the rows at the top...
=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")
Answers
-
Are you able to provide a screenshot for reference?
-
Hi paul, thank you for ur time,,,
There are three materials named AAA, BBB, and CCC. I would like to submit a request to offer material AAA from 5th September 2021 to 7th September 2021. it has to compare material AAA customer list ,However, this period clashes with material AAA’s Customer 2 slot (4th September 2021 to 9th September 2021) and therefore it should indicate red symbol in Availability column . If my intended request period does not clash with material AAA’s Customer slots, then should indicate as green symbol.
The second material that I would like to request for is material BBB, from 24th September 2021 to 25th September 2021. now it has to compare material BBB customer list ,it clashes Material BBB’s Customer 1 slot (23rd September 2021 to 28th September ) therefore it should indicate red symbol in Availability column.
can u pls help me out
-
My suggestion would be a helper column with the following in the child rows:
=PARENT(Infos@row)
Now that we have the material also listed on the child rows, you can use this to flag the rows at the top...
=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")
-
hello paul,,, as per ur instruction
i created a Helper column also specified the formula =PARENT(Infos@row) for that column
and i tried out next formula on Availability column
=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = Infos@row, Startdate:Startdate, @cell <= Enddate@row, Enddate:Enddate, @cell >= Startdate@row) = 0, "Green", "Red")
i got like this,,can u pls have a look..
thanks for ur help
-
oh ok,, i did a mistake in Helper Column name,,,
That perfectly great,,,, works fine👍️
Thnak you so much
-
Is it possible to specify availability formula only for chilrows under first parent
i mean i want to show availability status only under request parent childs
-
I'm not sure I understand your question.
-
Ok, Actually i clicked "Covert to cell formula" so it shows availability status for all cell.
i would like to show Availability status only for Request parent Child not for Material parent and Material parent Child.
-
The helper column can actually be hidden. The availability formula would only go on the rows in the Request section.
-
perfect!!!! thanks for your help!!
-
Happy to help. 👍️
-
Hi. Mr .Paul,
i have a simliar problem to compare, bit different way,, can you please me out
https://community.smartsheet.com/discussion/83979/compare-time#latest
Thank you for your help...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!