Helper column for date conversion

I have a column in a sheet that is set to pull the date and time a work order was created. I am trying to add a helper column to convert this column to date only but it doesn't seem to be pulling in the correct year. Can anyone help me with my formula?

=DATE(VALUE("20" + MID([Date WO Created]@row, FIND("/", [Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1) + 1, 2)), VALUE(LEFT([Date WO Created]@row, FIND("/", [Date WO Created]@row) - 1)), VALUE(MID([Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1, FIND("/", [Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1) - (FIND("/", [Date WO Created]@row) + 1))))

Find more posts tagged with

Accepted answers

Monique Odom Stearn

Hello @katie.mcelroy,

Could you simplify your formula by simply pulling everything before the space? Try:

=LEFT([Date WO Created]@row,FIND(" ",[Date WO Created]@row))

All comments

Monique Odom Stearn

Hello @katie.mcelroy,

Could you simplify your formula by simply pulling everything before the space? Try:

=LEFT([Date WO Created]@row,FIND(" ",[Date WO Created]@row))

katie.mcelroy

That fixed it. Thank you!

katie.mcelroy

I have another sheet that is pulling data from the one I converted the date on. I am using the below formula, referencing the created column, to try to get the # of work orders during q1 of this year and it's returning a 0 value. Any idea why that would be?

=COUNTIF({WO Log - Created}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))

Genevieve P.

Hey @katie.mcelroy

That formula worked for me.

Is it possible that sheet has 0 rows that were created between January and March? Can you add a filter on the source sheet to check?

katie.mcelroy

No there are definitely rows during that time frame. I tried the filter but it's showing me rows that were created well outside that date range.

katie.mcelroy

Nevermind, I fixed the filter and now it's showing correctly. Still not sure why my other sheet is pulling in 0.

Genevieve P.

Hi @katie.mcelroy

Thanks for posting screen captures, this helps! I see that you're using a formula to pull the data into your Created column. Can you clarify what formula you're using? Is it possible that it's outputting text instead of a date?

katie.mcelroy

It seems as if my helper column (Created) is not recognizing the column as date. I created another test column (date?) to see if the helper column was pulling in as date and I got back no.

The formula in the above screenshot is the formula I'm using in my helper column to convert the Date WO created column to a date only smartsheet will recognize. It's obviously not working. My columns are set to date types.

Genevieve P.

Hi @katie.mcelroy

Thank you, this helps! Yes, you're converting the date into text with your current formula.

Instead, try this:

=DATEONLY([Date WO Created]@row)

See: DATEONLY Function

This should retain the values as Dates and then your other formula will work correctly.

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.