DATE Formula results in Next Day when Request is Submitted after 8pm

Hi, for our ticketing system, I am using a DATE formula to only pull the date (Date column) from when a request comes in (Request Date column). I use that "Date" as part of formula to calculate how long it took us to complete the ticket. But as you can see in my screenshot, if a request comes in after 8PM (I am on the East Coast), the DATE Column populates the next day. So what happens if we complete a ticket the same day and the DATE column populates the next day after it is submitted, I am getting a negative number for Ticket Duration. Anyone know why this is happening?

Thanks!

Tags:

Answers

  • Hi @BristolCVN !

    That's weird ! Maybe it has something to do with your working day schedule ? If you have set it say from 8am to 8pm, maybe the system figures that it's the next working day after 8pm ?

    I don't know, just guessing. Might be helpfull !

    Have a great day !

    Lynda :)

  • BristolCVN
    BristolCVN ✭✭✭✭

    Hi Lynda, can you clarify on "working day schedule"? Is that a setting somewhere?

    Thanks

  • SSFeatures
    SSFeatures ✭✭✭✭✭

    Hi @BristolCVN!

    SmartSheet stores dates and times using Greenwich Mean Time (GMT) rather than using your account's time zone settings. See Here

    Since you're on the east coast, you experience this problem at 8 PM because there is a 4 hour time gap between eastern time and GMT time.

    There's not really a good "clean" solution to make this work directly in your SmartSheet settings. The best idea that I have at the moment is for you to change your column's formula. One idea is to create another column named Request Date (EST) which subtracts 4 hours from your Request Date column, and then you use Request Date (EST) for your formula.

    Another idea is to add an IF into your Date formula that checks if the time is >= 8PM and < 12PM, and if it is then subtract 1 day from the date.

    These are a couple ideas.

    I hope this helps explain the problem!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

  • BristolCVN
    BristolCVN ✭✭✭✭
    edited 10/03/24

    Thanks so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!