Need to calculate number of days to completion
Answers
-
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.
-
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?
-
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.
-
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)
-
@Paul Newcome. I got that formula to work
-
Excellent. Happy to help. 👍️
-
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
-
@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.
-
Hey @Paul Newcome
That seems to have done it for now. Thanks for your help (again!).
-
Happy to 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!