Find the latest time entry and send a reminder if theres no entries in the future

stuartr

Hi. I have built a form for our HR department to log their whereabouts for the following week. The team also receive an automated reminder every Wednesday to submit 5 entries (one row = 1 day) for each day of the following week. They simply advance the date field and select their whereabouts for each day.

Although this is working well, the HR manager has asked if I can now create an additional reminder to be automatically sent every Monday morning for all team members who still haven't yet submitted for that week.

I think I need a MAX formula to identify the latest date (select date) submitted for each team member (HR team Member). I created a checkbox field for this with the formula below but I can't get it to work.

=IF([Select Date]@row = MAXCollect([Select Date]:[Select Date],[HR team Member]:[HR team Member], [HR team Member]@row)), 1)

Once the checkbox is working, I thought of creating a separate summary/metrics sheet, then sending an automation from there every Monday, where the most recent select date value is in the past.

Can anyone assist with the formulae above? And is there a cleaner approach?

Your help as ever is appreciated.

KPH

Hi

Your MAX COLLECT is missing a parenthesis after MAX

MAX(COLLECT([Select Date]:[Select Date],[HR team Member]:[HR team Member], [HR team Member]@row))

This will now return the latest date for that team member. Putting that in the IF will check the box if the current row is the latest date. That step may be unnecessary as you already know what the date is. I would instead compare the MAX date with today’s date.

This formula (which could go on your summary/metrics sheet)

= IF(MAX(COLLECT(……))<TODAY(),1)

would check a box for each row belonging to a team member whose latest date is in the past. So if they complete this mon-fri, don’t update, then on Saturday this will be checked. You could give 2 days grace period by subtracting 2 days from today so this will be checked on Monday.

= IF(MAX(COLLECT(……))<TODAY(-2),1)

You could then use this in the automation.

Does that help?

KPH

Hi

Your MAX COLLECT is missing a parenthesis after MAX

MAX(COLLECT([Select Date]:[Select Date],[HR team Member]:[HR team Member], [HR team Member]@row))

This will now return the latest date for that team member. Putting that in the IF will check the box if the current row is the latest date. That step may be unnecessary as you already know what the date is. I would instead compare the MAX date with today’s date.

This formula (which could go on your summary/metrics sheet)

= IF(MAX(COLLECT(……))<TODAY(),1)

would check a box for each row belonging to a team member whose latest date is in the past. So if they complete this mon-fri, don’t update, then on Saturday this will be checked. You could give 2 days grace period by subtracting 2 days from today so this will be checked on Monday.

= IF(MAX(COLLECT(……))<TODAY(-2),1)

You could then use this in the automation.

Does that help?

stuartr

Wow, that's brilliant, so helpful.This community is great. Thank you for your help. I'll let you know if that works

KPH

👍 Shout out if you have any problems with it.

stuartr

Hi KPH I'm so glad you said that so...

1) So this formula on checkbox col1 now works

=IF([Select Date]@row = MAX(COLLECT([Select Date]:[Select Date], [HR Team Member]:[HR Team Member], [HR Team Member]@row)), 1)

2) But the above formula used on new checkbox col 2 with your "in the past" formula now added at the end does not work

=IF([Select Date]@row = MAX(COLLECT([Select Date]:[Select Date], [HR Team Member]:[HR Team Member], [HR Team Member]@row))<TODAY(),1)

Any idea where I went wrong?

------------------

Also (this is less important but would be good to know) I'm having to keep everything on the original sheet. This is because when I try and recreate the formula from a separate / new metrics sheet (which contains only 2 columns, the HR Team Member names (populated) and a checkbox column to identify dates where the last entry for that team member is in the past, it returns the range from the first sheet as {Select Date} ---ie {curly brackets} - I have tried using these {} to reconstruct the formula above but didn't know if I still needed the square brackets [..] as well as the curly {} or indeed if I still needs the colon ]:[ between the square brackets. So for example I tried..

=IF({Select Date}@row = MAX(COLLECT([{Select Date}]:[{Select Date}]...etc

=IF({Select Date}@row = MAX(COLLECT([{Select Date}], [{HR}]....etc

but neither work....

I appreciate your time on this but the first point is the priority please.

Stuart

KPH

Hi Stuart

1/2

You have part of formula 1 in formula 2 that you don't need (and won't work). Your formula 2 is saying if the select date on the row equals the max collected date for that person ... less than today. Which doesn't make sense. You do not need to include the [select date]@row (which will also help with the other less important part). You just want to say if the max collect date for the person is less than today:

Try:

=IF(MAX(COLLECT([Select Date]:[Select Date], [HR Team Member]:[HR Team Member], [HR Team Member]@row))<TODAY(),1)

You should get something like this:

KPH

2/2

"Less important part"....

When you enter the formula on the metrics sheets all the columns from the source sheet should be referenced individually and entered between curly brackets. The column on the metrics sheet will be within square brackets. Because your cross sheet reference is (should be) to an entire column you don't need the colon. You are aiming for something like this (reference names will vary):

=IF(MAX(COLLECT({source sheet select date}, {source sheet team member}, [HR Team Member]@row))<TODAY(),1)

You need to create each of those references by clicking on the reference another sheet link in the formula help pop up that will appear as you type the formula.

Select your source sheet from the tree.

Select the column and enter the name you want to use (good practice is to include something to identify the sheet and the column).

The reference will be added to the formula:

Continue entering the formula (in this case add a comma).

Then repeat to reference the next column.

Continue with the formula:

See also: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

You don't need all these formula, either the one on the original sheet, or the one on the metrics sheet can be used to trigger your alert (or your original formula 1).

stuartr

Once again thank you so much. This makes sense and it is now working.

Thanks also for the explanation of the brackets - I was aware that {} references another sheet, but I still wasn't sure if you needed the [] in addition if there was a space in the field name of the reference sheer ie {HR Team} would still need to be {[HR team]:[HR Team]}.

But you have explain that it doesn't so that clears that up too.

KR Stuart

KPH

Wonderful news! Glad you have that all working.