Formula to Return Modified Date When Dropdown is Selected

Good afternoon, I'm trying to write a formula on one sheet (METRICS 1 - M&O Ingrants) that will record the modified date when a dropdown is selected in another sheet (M&O Ingrants). I want to do this so I can track the duration it takes to move from one dropdown selection to another because it's tracking status of a project. So far, I set up a workflow to copy the row from the original sheet (M&O Ingrants) to another sheet (METRICS 1 - M&O Ingrants). The new/different columns in the Metrics sheet are the selections in the dropdown column from M&O Ingrants. All I want it to do is record the dates that the selection was made in the corresponding columns.

Right now, I have =IF(AND({Status} = "Upcoming PREP", {Project} = Project1), {Modified}) where {Status} is the dropdown column in the original sheet, "Upcoming PREP" is one option to select from the dropdown, {Project} is the task list in original sheet, and {Modified} is the Modified Date column from the original sheet. This formula is returning #INVALID OPERATION. Any thoughts or help? I understand I can manually retrieve these dates from the Activity Log, but that requires a lot of manual labor which could be fixed with a formula.

Best Answer


  • Hi@David Joyeuse, I added {Status}="Upcoming PREP" to your formula and it's working so far. Hopefully, it will hold up as more rows and statuses are populated. Thank you for your help!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!