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?
-
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.
-
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"), ""))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!