Formula to Return Modified Date When Dropdown is Selected

Sarah Watts

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.

David Joyeuse

Hi @Sarah Watts

You are asking for an IF function to return a range here in your formula.

What you want to do here is more within the lines of a COLLECT function than an IF.

More like:

=JOIN(COLLECT({Modified}, {Project}, [Project]@row))

I wouldn't use the status column in the function at it is changed on the first sheet prior to the row copy, so you shouldn't be able to find it back. Rather than that, use any other column you may think useful to sort the COLLECT function.

Hope it helped!

David Joyeuse

Hi @Sarah Watts

You are asking for an IF function to return a range here in your formula.

What you want to do here is more within the lines of a COLLECT function than an IF.

More like:

=JOIN(COLLECT({Modified}, {Project}, [Project]@row))

I wouldn't use the status column in the function at it is changed on the first sheet prior to the row copy, so you shouldn't be able to find it back. Rather than that, use any other column you may think useful to sort the COLLECT function.

Hope it helped!

Sarah Watts

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!!