Help with a late application formula

DHR Temp Assignment Team

Hi community! I have just created a smartsheet to collect applications for positions within our organization - however, because i used to log employees who applied outside of smartsheet in the smartsheet, i have employees who will show as applying LATE based on the formula i currently am using - is there a way that I can make the formula

only start on a specific date (i.e., when we launch the application through smartsheet this coming Monday?) I have tried etc. and it does not seem to work - here is the formula i am using:

=IFERROR(IF(IF([RECEIVED DATE]@row = "", "", [RECEIVED DATE]@row) > MAX([EXTENDED/READVERTISED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late"), "")

Paul Newcome

Could you adjust the [Received Date] to be the actual date of receipt instead of the entry date?

DHR Temp Assignment Team

Hi Paul, from what I understand from smartsheet support, there is no date when a form is submitted - hence, we need to use the system date (i have named it Received Date for our purposes) but basically, the form submission creates the row and the form being submitted is the application, so that date is the one that is needed but we didn't have this in place for 100's of applications and I'm trying to find a way to remove the older rows from the formula. Hope that helps - I appreciate your quick response.

Paul Newcome

Hmm...

What if you created a second date column and then use a formula to pull the date from the date/timestamp column? Then you could reference this date field in your main formula, but it will allow you to manually update the "submitted" date in that column for the late entries.

DHR Temp Assignment Team

could I do the reverse? we will receive hundreds of applications when we convert to the smartsheet form - and we have about 200 old entries that I would like to override "late" with nothing; I don't understand why it won't let me add the greater than to the formula - is it because it is a system formula?

Paul Newcome

If you are using this as a cell formula, could you just delete the formula from those rows so that "Late" doesn't show? If you are using a column formula, you could create a checkbox column called (for example) [Late Entry], check those boxes, then add an IF statement to the beginning to say that if the box is checked then leave blank, otherwise run original formula.

=IF([Late Entry]@row = 1, "", original_formula)

=IF([Late Entry]@row = 1, IFERROR(IF(IF([RECEIVED DATE]@row = "", "", [RECEIVED DATE]@row) > MAX([EXTENDED/READVERTISED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late"), ""))