Creating a formula to calculate Created Date column - not working correctly

If I am trying to create a metric formula to count how many rows were created today, created yesterday, created 3 days ago etc… by using the auto "Created Date" column. The current formula I am using is not properly adding them correctly

=COUNTIFS({Ticket Tracker Range 1}, "COUNTING" , {Ticket Tracker Range 3}, =TODAY(-1))

^^ this is supposed to count all rows with the word "counting" is in a certain column and the Created Date was Yesterday. I know my formula isn't working because when I manually count them they do not add up to the same number that the formula is calculating. I tried to create a manual 'helper' date column that is date only and it removes the time but that doesn't even work because it that helper date column isn't always documenting the correct date because the time affects it.

Any suggestions on how to fix this or make a different formula to calculate?

Find more posts tagged with

Formulas

Accepted answers

All comments

=Chris Palmer

Hello @Lyndie,

Something like this perhaps?

=COUNTIFS({Ticket Tracker Range 1}, "COUNTING", DATE({Ticket Tracker Range 3}), TODAY(-1))

Using the DATE() function may work with stripping out the time.

Lyndie

It is telling me that it is an Incorrect Argument.

jmyzk_cloudsmart_jp

Hi @Lyndie

When you are dealing with a system column like Created Date, you need to be aware that the display value and internal or back-end value are not the same.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Time zones and system columns
System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

In the demo sheet below, since my time zone is UTC+9 in Tokyo, any Created date before 9:00 AM shows yesterday when I apply the ISDATE function, whereas it displays today.

https://app.smartsheet.com/b/publish?EQBCT=3320fb06b55146babf8b36cb24b9ec5c

To calculate Created Date as you see it in the system column, you can use the combination of the DATE function and text functions, like the MID or LEFT, and the VALUE function.

=DATE(
  VALUE("20" + MID([Created date]@row, 7, 2)), 
  VALUE(LEFT([Created date]@row, 2)), 
  VALUE(MID([Created date]@row, 4, 2))
)

Once you get the desired date, you can reference it in your cross-sheet reference formula.

Lyndie

Yeah I figured it had something to do with the time zone and I just could not get it right.

I will try this.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.