Need to calculate number of days to completion

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

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?

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

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)

Ashley McAdoo

@Paul Newcome. I got that formula to work

Paul Newcome

Excellent. Happy to help. 👍️

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

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

Ashley McAdoo

Hey @Paul Newcome

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

Paul Newcome

Happy to help. 👍️