Formula for cell to populate most recent cell activity in a selection of cells

Jillian M

I have an automation set to email a completion certificate when the 4th class of a series of 4 is completed. It works if all are taken in order; however, when any of the classes are the final class completed (besides the 4th), the automation doesn't work because it's using the 4th class' date but it's not seeing that all classes are complete, so it doesn't send the certificate. I believe I need a formula in a separate cell to denote the final class completion date so the automation runs from that cell's data instead of the 4th class. How would you best suggest to fix this??

Paul Newcome

Are you able to provide a screenshot for context?

Paul Newcome

I would suggest inserting 4 more columns that will house formulas to convert the text strings into dates. I also suggest making sure the manually entered dates are in the same format as the auto-populated dates so that we can use less complex conversion formulas.

=IFERROR(DATE(VALUE(LEFT([Class 1 Complete]@row, 4)), VALUE(MID([Class 1 Complete]@row, 6, 2)), VALUE(MID([Class 1 Complete]@row, 9, 2))), "")

Then you would use a standard MAX function across the 4 conversion columns to pull in the final completed date.

=MAX([Class 1 Date]@row:[Class 4 Date]@row)

Paul Newcome

Are you able to provide a screenshot for context?

Jillian M

of course! See how class 1 was completed after 2, 3, and 4? The automation is set to capture the Class 4 Complete date (when all 4 classes are complete) to send the certificate. In this instance, the certificate didn't send because when class 4's cell was filled with the date, class 1 was still empty. When class 1 was complete, it didn't trigger because it wasn't class 4. i hope that makes sense…so I believe I need a seperate cell that will fill in (or copy so to speak) the date of the last completed class of these 4 so the automation will use that date and not any of these per se.

Paul Newcome

DO you also need the time, or is just using the date ok?

Jillian M

just the date is fine. Right now it's pulling everything into the certificate "date" section so it looks kinda weird with the date and the time 😀

Jillian M

And sometimes I free text the date in if I have confirmation they attended but just didn't complete the survey to confirm their attendance. The key is that there's some sort of date in some sort of format in the cell.

Paul Newcome

I would suggest inserting 4 more columns that will house formulas to convert the text strings into dates. I also suggest making sure the manually entered dates are in the same format as the auto-populated dates so that we can use less complex conversion formulas.

=IFERROR(DATE(VALUE(LEFT([Class 1 Complete]@row, 4)), VALUE(MID([Class 1 Complete]@row, 6, 2)), VALUE(MID([Class 1 Complete]@row, 9, 2))), "")

Then you would use a standard MAX function across the 4 conversion columns to pull in the final completed date.

=MAX([Class 1 Date]@row:[Class 4 Date]@row)

Jillian M

Thank you so much…I copied the first formula into the 1st of 4 new columns but I'm getting an #INVALID COLUMN VALUE error. Is it something I did/didn't do?

Paul Newcome

Make sure you are putting it in a date type column.

Jillian M

Genius! It worked! I think I'm good to go. Thank you soooo much!

Paul Newcome

Happy to help. 👍️