How to validate a sheet cell when user saves the record?

Matt Rasmussen

Is there a way to validate when a sheet cell contains incorrect data and tell the user to correct it then WHILE STILL IN THE SHEET, without sending an email for the user to go back and correct it? My scenario is that we want people to report how many hours they worked on a project for a given week and use the week ending date. We use weeks starting on Monday and ending on Sunday so the week ending date should always be a Sunday date.

1) I have a hidden "helper column" that uses the WEEKDAY() function to determine the day of the week the user entered in the week ending date cell:

=WEEKDAY([Week Ending Date]@row)

2) I have another hidden "helper column" that has a large IF() statement using the WEEKDAY() function to calculate the next Sunday after the date that was entered.

=IF(WEEKDAY([Week Ending Date]@row) = 1, [Week Ending Date]@row, IF(WEEKDAY([Week Ending Date]@row) = 2, [Week Ending Date]@row + 6, IF(WEEKDAY([Week Ending Date]@row) = 3, [Week Ending Date]@row + 5, IF(WEEKDAY([Week Ending Date]@row) = 4, [Week Ending Date]@row + 4, IF(WEEKDAY([Week Ending Date]@row) = 5, [Week Ending Date]@row + 3, IF(WEEKDAY([Week Ending Date]@row) = 6, [Week Ending Date]@row + 2, IF(WEEKDAY([Week Ending Date]@row) = 7, [Week Ending Date]@row + 1, TODAY())))))))

I want to notify the user they entered a Friday (or whatever day) and tell them to use the Sunday date. Many times people enter their time at the last minute on Friday and then leave for the weekend so sending an email at the top of the hour will be a missed opportunity. Some people will record vacation days on separate records of the sheet for each vacation day taken and use that weekday's date in the "Week Ending Date" column so I'd like to give them an opportunity to fix the records.

I can't find a method to tell user while still on the sheet. Is it possible? Any best practices or suggestions on how to correct the data?

Paul H

Create a new column called "Notice" with this

=IF(WEEKDAY([Date Entered]@row) <> 7, "End date must be a Sunday", "Validated")

Create a conditional format to change the date red

Paul H

Create a new column called "Notice" with this

=IF(WEEKDAY([Date Entered]@row) <> 7, "End date must be a Sunday", "Validated")

Create a conditional format to change the date red

Matt Rasmussen

Thank you @Paul H for the suggestion. I tested it and it works. However, it forces the Notice column to be next to the Date Entered column and always be displayed which clutters up the sheet. It would be better functionality if Smartsheet had a way for the message in the notice column to be displayed as a pop-up.