how to compare one parent child value with another parent child value?

PYU

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

Paul Newcome

My suggestion would be a helper column with the following in the child rows:

=PARENT([email protected])

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 = [email protected], Startdate:Startdate, @cell <= [email protected], Enddate:Enddate, @cell >= [email protected]) = 0, "Green", "Red")

Paul Newcome

Are you able to provide a screenshot for reference?

PYU

Hi paul, thank you for ur time,,,

Screenshot _sheet1.png

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

Paul Newcome

My suggestion would be a helper column with the following in the child rows:

=PARENT([email protected])

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 = [email protected], Startdate:Startdate, @cell <= [email protected], Enddate:Enddate, @cell >= [email protected]) = 0, "Green", "Red")

PYU

hello paul,,, as per ur instruction

formula2.png

i created a Helper column also specified the formula =PARENT([email protected]) for that column

and i tried out next formula on Availability column

=IF(COUNTIFS([Helper Column]:[Helper Column], @cell = [email protected], Startdate:Startdate, @cell <= [email protected], Enddate:Enddate, @cell >= [email protected]) = 0, "Green", "Red")

i got like this,,can u pls have a look..

thanks for ur help

PYU

oh ok,, i did a mistake in Helper Column name,,,

That perfectly great,,,, works fine👍️

Thnak you so much

PYU

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

Paul Newcome

I'm not sure I understand your question.

PYU

Status.png

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.

Paul Newcome

The helper column can actually be hidden. The availability formula would only go on the rows in the Request section.

PYU

perfect!!!! thanks for your help!!

Paul Newcome

Happy to help. 👍️

PYU

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...