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

08/31/21
Accepted

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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")

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to provide a screenshot for reference?

    thinkspi.com

  • 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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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")

    thinkspi.com

  • hello paul,,, as per ur instruction

    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

  • 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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I'm not sure I understand your question.

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • perfect!!!! thanks for your help!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

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

Sign In or Register to comment.