Help with a late application formula

Options

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"), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • DHR Temp Assignment Team
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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"), ""))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!