Last recorded date by User - 2 month reminder

Question

Hi All,

I want to create a simply usage log for equipment (CMM), we want users to keep skills current, so they record when they log-in.

We then want automated reminders / notifications if the user hasn't used the equipment for 2 months ('Related Email Address' field).

The puzzle for me is that there will be multiple entries in the sheet for each user, but I only want the notification to trigger from the most recent date log-in for individual users.

Thank you in advance for any help.

LeelaLodhi03 · Answer

Hi @Richard A, You can handle this by flagging only the latest login per user, then triggering the alert from that row,add a helper column with:

=IF(AND(

NOT(ISBLANK(User@row)),

NOT(ISBLANK([Login Date]@row)),

[Login Date]@row = MAX(COLLECT([Login Date]:[Login Date], User:User, User@row)),

TODAY() - [Login Date]@row >= 60

), 1, 0)

Then set automation on Reminder Flag = 1 and enable only trigger when condition changes to true.

Please test with sample data and adjust column names as per your sheet.

Thanks.

Paul Newcome · Answer

I would insert a flag type column and use this formula to flag the most recent row for that user for that equipment if the date is more than 2 months in the past.

=IF(Date@row = MAX(COLLECT(Date:Date, [Related Email Address]:[Related Email Address], @cell = [Related Email Address]@row)), IF(Date@row < IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, DAY(TODAY())), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 10, DAY(TODAY()))), 1))