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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • sure please see below:


  • Kelly Moore
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • Paul Newcome
    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
    edited 02/08/21

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

       

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!