Helper column for date conversion

katie.mcelroy

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))))



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))

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