Need to calculate number of days to completion

2»

Answers

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭

    @Paul Newcome


    Thank you. That worked great on the existing rows, but how will that work for new rows that are input to the original source sheet? I set up an automation to copy the rows when a new one is entered, but that doesn't seem to work the same way.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So a new row is added to the sheet... The copy row automation is going to give us one row on the copy sheet (the initial row), but our formula needs two rows to be able to calculate.


    In those cases, the performed date would be blank upon initial entry, correct?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭

    Actually the performed date will have data for any new piece of equipment. I could be the installation date or they may have had to perform maintenance at installation.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Let's try an adjustment to the INDEX/COLLECT portion:

    =IFERROR(INDEX(COLLECT({Copy Sheet Due Date Column}, {Copy Sheet Indicator Column}, @cell = [Indicator Column]@row), COUNTIFS({Copy Sheet Indicator Column}, @cell = [Indicator Column]@row) - 1), [Due Date]@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭
    Answer ✓

    @Paul Newcome. I got that formula to work

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent. Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭

    Hi @Paul Newcome,


    This project got put on hold and now that I'm back on it the formula isn't working, probably because of changes made to the sheet, and I can't even figure out what I'm supposed to reference 🙄.

    I have this formula in the (Date Type) Index/Collect column on the source sheet. "Copy Sheet" references the sheet that the rows get copied to.

    =INDEX(COLLECT({Copy Sheet Date Month Due}, {Copy Sheet Capture Serial Numbers}, @cell = [Capture Serial Numbers]@row), COUNTIFS({Copy Sheet Capture Serial Numbers}, @cell = [Capture Serial Numbers]@row) - 1)

    and I am getting DATE EXPECTED error.

    If I add IFERROR to it,

    =IFERROR(INDEX(COLLECT({Copy Sheet Date Month Due}, {Copy Sheet Capture Serial Numbers}, @cell = [Capture Serial Numbers]@row), COUNTIFS({Copy Sheet Capture Serial Numbers}, @cell = [Capture Serial Numbers]@row) - 1)

    I get INCORRECT ARGUEMENT SET


    Can you please tell me what is wrong?


    Thanks,

    Ashley

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ashley McAdoo Are you trying to pull in dates or some other type of data? The first formula in your last post is throwing an error that indicates it is working but it is pulling in non-date values into a date type column. If you are intending to pull in non-date values, just change the column type the formula is in to a text/number.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ashley McAdoo
    Ashley McAdoo ✭✭✭✭✭

    Hey @Paul Newcome

    That seems to have done it for now. Thanks for your help (again!).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!