Request Tracker Form

Hans Millican

Hello, I have a couple of questions. I am looking for the formula in the "estimated days to completion" column. It doesn't appear to be included (Screenshot 1). Also, how do I reset the request ID numbering to start at 0 again (Screenshot 2)

? I tried deleting the rows and that seemed to cause all sorts of problems. Thanks for the input!

Paul Newcome

@Andrée Starå It looks like the formula you provided is missing a minus sign there towards the end...

=IF([Date Need By]@row = [Estimated Completion Date]@row; "On Time"; [Estimated Completion Date]@row - [Date Assigned]@row)

Andrée Starå

@Paul Newcome Yes, noticed that when I looked at the sheet. I've shared the correct one with Hans so it's probably working now.

Paul Newcome

If you double click on a cell that has data in it as if you are going to edit the cell in the [Estimated Days To Completion] column, what appears?

As for the Request ID column, you should be able to delete the column, save the sheet, refresh your browser, then re-add the column.

Hans Millican

Only the text appears in the column (i.e. On Time, 1, 2, -3, etc.). Tried to delete, and save form without column, but still wants to keep the numbering the same.

Paul Newcome

Was there ever a formula in that column?

Hans Millican

It does not appear to me that a formula existed in any of the locked columns. The other two are easy enough, but the one I have a question about would take some work to figure out.

Paul Newcome

Do you know the logic behind what the formula was supposed to do? Maybe we can rewrite it.

Hans Millican

The formula in the “Estimated Days to Completion” column shows “On Time” if the request can be

completed by the requested need by date. If the request can be completed early or if it will be late, you

will see the number of days the request will be ahead (negative value) or behind (positive value).

○ For example, -3 means the request can be completed 3 days ahead of the requested need by

date and (positive) 2 means the completion of the request will be delayed by 2 additional days.

Paul Newcome

Ok. And what are the column names used to generate this logic?

Hans Millican

That part I don't know for sure. I assume it is with the estimated completed date column and the date needed by column (That column is not in my screenshots)

Paul Newcome

Try searching the Activity Log for formulas and see if one was ever there. If there was, then you should be able to copy/paste directly from the Activity Log itself.

Andrée Starå

Hi Hans,

@Hans Millican

To reset the Auto-Number.

1. Change the column to Text/Number
2. Delete the content in the column
3. Save
4. Refresh the sheet
5. Change the column back to Auto-Number and set the Starting Number to 0
6. Save

Formula

Try something like this.

=IF([Date Need By]@row = [Estimated Completion Date]@row; "On Time"; [Estimated Completion Date]@row [Date Assigned]@row)

The same version but with the below changes for your and others convenience.

=IF([Date Need By]@row = [Estimated Completion Date]@row, "On Time", [Estimated Completion Date]@row [Date Assigned]@row)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Hans Millican

The auto number reset worked great! Still trying to get the function to work...I am getting an UNPARSEABLE error I can't figure out.

Andrée Starå

@Hans Millican Glad to hear that the Auto-Number is solved.

Regarding the formula.

I'd be happy to take a look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hans Millican

Thanks, just shared

Paul Newcome

@Andrée Starå It looks like the formula you provided is missing a minus sign there towards the end...

=IF([Date Need By]@row = [Estimated Completion Date]@row; "On Time"; [Estimated Completion Date]@row - [Date Assigned]@row)

Andrée Starå

@Paul Newcome Yes, noticed that when I looked at the sheet. I've shared the correct one with Hans so it's probably working now.