Help with a late application formula
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
-
Could you adjust the [Received Date] to be the actual date of receipt instead of the entry date?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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"), ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!