Smartsheet marking items late -
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
-
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.
-
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...
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!