Need to calculate number of days to completion

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?



Best Answer



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!