Late application formula
Hi- I have 3 date fields that I would like to compare to determine if an application is late (app status):
app submitted
final filing date
extended filing date
app status ( late )
if app is submitted on 1/15/21 and the final filing date is 1/15/21 the app status should be BLANK; if there is an extended filing date of 1/20/21 the app status field should still be blank; if the app is received 1/16/21 the app status should be blank (as the filing date was extended).
If there is no extended filing date and the final filing date is 1/15/21 and app is received on 1/16/21 the app status should say LATE; if there is an extended filing date of 1/21 and an app is received 1/22/21 app status would be LATE - so the app status field needs to first determine if there is an extended date - if not, calc the app sub date against the final filing date; if there is an extended filing date, the app status should calc the app sub date against the extended filing date.
I have had this working but it does not stick ... it will note remove a LATE from the column if I make changes... also it is regarding the app sub date and the ffd or effd as = when I use (i.e., app sub 1/5/21, ffd 1/5/21 should not be LATE - thank you
Best Answers
-
Are you able to provide a screenshot that shows the layout of your current sheet?
-
I would suggest this...
=IF(IF([APPLICATION SUBMITTAL DATE]@row = "", TODAY(), [APPLICATION SUBMITTAL DATE]@row)> MAX([EXTENDED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late")
Basically what this does is pulls the Submit Date if there is one or uses TODAY if there is not. It then compares it to whichever date is greater between the final filing or the extended filing date. If it turns out that the TODAY/Submit Date is greater than the larger of the two deadline dates, then it will output "Late". Otherwise it will remain blank.
Answers
-
Are you able to provide a screenshot that shows the layout of your current sheet?
-
sure please see below:
-
Try this
=IF(ISDATE([APPLICATION SUBMITTAL DATE]@row), IF(AND([EXTENDED FINAL FILING DATE]@row = "", [FINAL FILING DATE]@row > [APPLICATION SUBMITTAL DATE]@row), "Late", IF(ISDATE([EXTENDED FINAL FILING DATE]@row), IF(AND([FINAL FILING DATE]@row > [EXTENDED FINAL FILING DATE]@row, [FINAL FILING DATE]@row > [APPLICATION SUBMITTAL DATE]@row), "Late"))))
Cheers
-
I would suggest this...
=IF(IF([APPLICATION SUBMITTAL DATE]@row = "", TODAY(), [APPLICATION SUBMITTAL DATE]@row)> MAX([EXTENDED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late")
Basically what this does is pulls the Submit Date if there is one or uses TODAY if there is not. It then compares it to whichever date is greater between the final filing or the extended filing date. If it turns out that the TODAY/Submit Date is greater than the larger of the two deadline dates, then it will output "Late". Otherwise it will remain blank.
-
Thank you both. Paul's formula worked out well - the other one seems to not be able to pick up the final filing date, so it is marking all applications late.
Paul, is there a way to modify the formula so that if there is NO application submitted date (blank), the formula does not show #invalid. there will be times when there is no date in that field (or the comparable fields) due to an exemption to the ad process (they don't apply, but are automatically selected).
Thank you!!!!!
-
There should not be an error if the submitted date is blank. This portion of the formula should be taking care of that...
=IF(IF([APPLICATION SUBMITTAL DATE]@row = "", TODAY(), [APPLICATION SUBMITTAL DATE]@row) > MAX([EXTENDED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late")
It replaces a blank field with today's date and then compares it to the appropriate deadline date. Reading it literally says "if the [Application Submittal Date]@row is blank, use TODAY, otherwise use the [Application Submittal Date]@row".
-
Hi - you are correct, however, it requires that there is at a minimum a final filing date - when an ad exemption comes through, it will not have any of the dates because it won't ever be advertised. I think the logic works perfectly, my mistake that I didn't mention that we could have times where we will never advertise but still use Smartsheet to capture the candidate info through the ad form
-
Ah. Ok. My apologies. Try this...
=IFERROR(IF(IF([APPLICATION SUBMITTAL DATE]@row = "", TODAY(), [APPLICATION SUBMITTAL DATE]@row) > MAX([EXTENDED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late"), "")
-
Thank you so much Paul - this is PERFECT!!!! You are a rock star!
-
Happy to help. 👍️
-
Hi Paul, me again! I just noticed that some cells have "late" even though there is no app submital date (this can be because the ad has an exception and will not require an application but it is not applied consistently (see attached). All of the blank app dates should not be late... they should only be late if they are later than the final filing date or the extended filing date. I'm trying to figure out why this is not working the same for the same row information... maybe I'm missing something. (sorry I didn't notice this earlier).
Formula:
=IFERROR(IF(IF([APPLICATION SUBMITAL DATE]@row = "", TODAY(), [APPLICATION SUBMITTAL DATE]@row) > MAX([EXTENDED/READVERTISED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late"), "")
-
@DHR Temp Assignment Team Are you able to provide a screenshot of the issue?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!