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!

Find more posts tagged with

Formulas

dates

Accepted answers

All comments

LyndaRousseau

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

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

Thanks

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!

BristolCVN

Thanks so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.