Need to calculate number of days to completion

Ashley McAdoo

Hello Community,

This is a more complex question than it seems.

I have a Due Date that is calculated off a Completion Date on a sheet where the Due Date and Completion Date cells are used over and over again. For example, if a piece of equipment requires a 6 month maintenance schedule, it will get input via a form with a Maintenance Completion Date of lets say 14 August, 2023, and the Due Date will automatically be calculated to [Completion Date + 180]. When the Due Date rolls around 6 months later and the 6 month maintenance is performed again, the tech will overwrite the previous completion date in the Completion Date cell with the new Completion Date, which will cause the Due Date to change to [New Completion Date + 180]. How can I capture how many days before or after the Due Date the task being completed since the Due Date is constantly changing?

I thought about copying each row to another sheet as soon as a new form is submitted/the sheet is populated, and then linking in all the possible (monthly, quarterly, etc. maintenance schedule options) Completion Dates on each row from the original sheet to the new sheet. That way Due Date is static, and Completion Date is dynamic. I don't want to have to manually link all four possible Completion Dates each time a row is copied to the Completion Sheet, but I can't seem to set it up so that the linkage is automatic.

Any ideas?

Thanks,

Ashley

Ashley McAdoo

@Paul Newcome. I got that formula to work

Paul Newcome

I have a few ideas, but are you able to post a few screenshots for context?

Ashley McAdoo

Hey @Paul Newcome ,

I attached a screen shot.

The Date Monthly MNTC Performed by Lab Tech is the last time that the monthly MNTC was done, and the Date Monthly Lab Tech MNTC Due is Performed Date +30. What I want to capture is for row 1 is how many days before or after 8/31/23 was the MNTC performed, but as soon as the new Performed Date is entered the Due Date changes.

I have automation built in to clear any data in the Lab Supervisor or QA columns with the trigger being changing the Performed Date, and after clearing the cells, SS sends an update request to the Lab Super and to QA to capyure their Review Dates.

MNTC Tracker.JPG

@Paul NewcomeThanks,

Ashley

Paul Newcome

My apologies, but I am not sure I fully understand. Let me give you a rundown of what I think is going on and you can point out where I am wrong.

My current Due Date is 8/1. I enter my Performed Date of 8/22, and you want to see that I was 21 days late.

The challenge is that when I enter my Performed Date, the Due Date automatically changes.

Is there a step or challenge I have missed?

Ashley McAdoo

@Paul Newcome - you have it exactly correct!

Paul Newcome

How about this... Make sure each row on the source sheet has a unique indicator on it then set up a second sheet with the same columns (save as new and leave existing data) then add more rows to this sheet (copy/paste existing) and in the top set of row you want to change the dates to the previous set of dates. This gives us the last set on top and the current set underneath of it.

Next we will set up a Copy Row automation on the working sheet that copies the row over to the copy sheet whenever the Performed Date changes. As rows are copied over, the copy sheet will have new rows inserted at the bottom of the sheet, so the "current" entries will always be the last rows in the sheet and the previous entry for each row will be the second from the last.

Next we can use an INDEX/COLLECT to pull the previous Due Date from the copy sheet based on the unique indicator and use a COUNTIFS to pull the second from the last.

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

Go ahead and get that set up to populate a temporary date column to make sure we are pulling in the right dates we want to calculate from there. Once we get this piece working, we can move on to the next piece.

Ashley McAdoo

Hey @Paul Newcome,

I must have done something incorrectly because I am getting a formula error. Here are screen shots of what I did.

Copy sheet with UI and 2 sets of the same rows.JPG

Source Sheet with UI.JPG

Invalid Column Value error.JPG

Thank you,

Ashley

Paul Newcome

There should be 2 entries for each on the Copy sheet. The one set (at the top) would be the previous due dates. The second set (on the bottom) would contain the current due dates.

Ashley McAdoo

Hey @ Paul Newcome. I did not have access to the previous dates, so I made up the data for the bottom set so there would be two different sets of dates.

Paul Newcome

The made up dates should be on the top set. The current dates would be the bottom set.

It may be tedious, but you might be able to get the previous dates from the cell history.

But now that you have two sets in, is the formula producing an error still? It should be pulling in the top set from the copy sheet.

Ashley McAdoo

@Paul Newcome,

The top set of dates are older, the bottom set is newer like the lab tech had just done all the monthly ones that are due. Is that not correct?

And this is how the sheet has been since you suggested that I create it, and I have the error the whole time.

Paul Newcome

Yes. It should be older dates up top and newer dates on the bottom.

Can you provide an updated screenshot of the copy sheet as well as the exact formula and error message again (just so I can try to keep things organized as we troubleshoot and build)?

Ashley McAdoo

Source Sheet with UI.JPG

Copy sheet with UI older dates on top and newer on bottom.JPG

Invalid Column Value error.JPG

Thank you!

Paul Newcome

Ok. So there's the problem. The Equipment Record Number column on the copy sheet doesn't have repeated numbers.

For the sake of example, lets say we have three pieces of equipment.

What you have:

1

2

3

4

5

6

What you should have

1

2

3

1

2

3

Ashley McAdoo

Gotcha. I fixed that by making the serial number the UI. The formula is working now.

Paul Newcome

Excellent. That was the hard part.

Now that we have the previous due date, all we need to do is subtract that from the current performed date. Negative numbers means it was performed ahead of schedule and positive numbers means it was performed late. If you wanted the negative/positive flipped around, then you can subtract the performed date from the previous due date instead.

=existing_previous_date_formula - [Performed Date]@row

You'll need to change the column properties the formula is in to text/number since we aren't outputting a date anymore.