Formulas not properly updating when referencing from another sheet

Hello!

I have been working with a set of formulas that do not appear to be calculating correctly when I am referencing that sheet from an external sheet. Here's my problem:

In my sheet, I have recorded a "created" column that uses smartsheet's auto generate date function to record the date that the line item was made. In order to get around issues with time zones, I have a "Created Date" column that uses the following formula to convert the date into it's correct format without getting messed up with time zones. (I'm GMT-6)

=DATEONLY(Created@row) - IF(AND(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 6, VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) < 12, FIND("P", Created@row) > 0), 1)

When i look at the sheet, I get this, and it looks great, 01/31/23 on the left becomes 01/31/23 on the right

However, when I try and reference this sheet from an external sheet to refer to the this bit of data, it looks like this. You can see that the "Created Date" no longer matches.

Anyone have any idea why the formulas would be inaccurate when referencing but correct when I'm viewing the sheet normally? I've tried referencing from completely fresh sheets and I still get the same issue.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Kleerfyre

When you actually make the link, does it show correctly or off like in the screenshot above of the cell link window?

Alec Filer

When I make the link, it is calculating incorrectly. It will calculate treating those highlighted dates as 2/01/2023.

Genevieve P.

Hi @Alec Filer

The Created Date system column will show you a display value time based on your preferences stored in your personal account, however the underlying value in the sheet is actually stored in UTC.

Here's the Help Article with information about this:

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones#toc-time-zones-and-system-columns

What I expect is happening here is that your time is displaying in PM on the 31st based on your personal preferences, but the underling date and time is actually into the next day in UTC. The formula in your sheet is grabbing the display text, but as soon as you look at this data across sheets it's getting the underling UTC time and date.

I would recommend using a Record a Date workflow instead of a formula.

You can set this to run once per day at a specific time, looking at all the rows that were added and don't have a date yet.

Notice that at the top you can specify what time the workflow runs each day, so newly added rows after that time will get the next day's date associated with it.

Will this work for you?

Cheers,

Genevieve

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.