Smartsheet marking items late -

DHR Temp Assignment Team

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

Mark 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:

https://app.smartsheet.com/b/mpublish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81&_ga=2.265034313.75954268.1618875735-1449845678.1618875735#sheet

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

Help?

Mark

Paul Newcome

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

DHR Temp Assignment Team

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

Paul 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?

DHR Temp Assignment Team

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 Newcome

Happy to help. 👍️