Smartsheet marking items late -

04/20/21
Answered - Pending Review

Hi (Paul, if you are listening!) I have a formula that determines if an application is on time or late -

It is marking applications that come in on the same day as the Final Filing Date as LATE (i.e., received date = 4/20/21 10 am, FFD 4/20/21 = LATE - however, these should be considered on time as the formula is saying the received date must be GREATER THAN the FFD or Ext FFD not = or greater... I'm at a loss how to fix this - I've created an "adj date - ss" column to fix any that are marked late, but what a PAIN... please let me know what I may be doing wrong! Thank you

=IFERROR(IF(OR([APPLICANT/CANDIDATE NAME]@row = "ADVERTISEMENT", [Adj Date - ss]@row <> ""), "", IF([RECEIVED DATE]@row > MAX([EXTENDED/READVERTISED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late")), "")

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @DHR Temp Assignment Team,

    Are you using a system generated date for Received Date or any of your other dates? If so, the cause of your issue is likely timezones. @Paul Newcome is the wizard of all things time. His solution for time zone correction is below:

    Convert your received date to your local time and use the adjusted date as DATEONLY() in your formula.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 04/21/21

    I would suggest starting with just working the DATEONLY function into your existing formula to see if that works.


    =IFERROR(IF(OR([APPLICANT/CANDIDATE NAME]@row = "ADVERTISEMENT", [Adj Date - ss]@row <> ""), "", IF(DATEONLY([RECEIVED DATE]@row) > MAX([EXTENDED/READVERTISED FINAL FILING DATE]@row, [FINAL FILING DATE]@row), "Late")), "")

  • Hi, Thank you - that DATEONLY does not work for some reason - it still marks the app late...

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Hmm... I think that Mark may be on to something with the time zones. What happens if you insert a date column and just put in

    =DATEONLY([RECEIVED DATE]@row)


    Do the dates match on every row, or are there some dates that don't match?

  • Hi, well this was a learning experience to say the least. Turns out that the received date is actually calculating UK time (don't ask me why when I have my settings to PST)... I used the following to create a helper column to change my received date to PST (it appears 5 hours is the difference we needed to make the formula accept as on time any items received by midnight).


    =DATEONLY([RECEIVED DATE]@row) - IF(AND(VALUE(MID([RECEIVED DATE]@row, 10, FIND(":", [RECEIVED DATE]@row) - 10)) >= 5, FIND("P", [RECEIVED DATE]@row) > 0), 1)


    Thank you again for all the help! I appreciate the support!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.