Late application formula

DHR Temp Assignment Team

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

Paul Newcome

Are you able to provide a screenshot that shows the layout of your current sheet?

Paul Newcome

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.

Paul Newcome

Are you able to provide a screenshot that shows the layout of your current sheet?

DHR Temp Assignment Team

sure please see below:

Kelly Moore

Hey @DHR Temp Assignment Team

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

Paul Newcome

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.

DHR Temp Assignment Team

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!!!!!

Paul Newcome

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".

DHR Temp Assignment Team

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

Paul Newcome

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

DHR Temp Assignment Team

Thank you so much Paul - this is PERFECT!!!!  You are a rock star!

Paul Newcome

Happy to help. 👍️

DHR Temp Assignment Team

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

   

Paul Newcome

@DHR Temp Assignment Team Are you able to provide a screenshot of the issue?