Need to formulate long date imported from form into a reportable date in Smartsheet

Lindsay Amys-Roe

I have a form (not Smartsheet and unable to convert to Smartsheet form) that populates into Smartsheet. Dates come in long form (I.E. September 30, 2019), which means they are not reportable in Smartsheet. I need to get the long form date into a date format Smartsheet recognizes so I can run reports off of it. 

I have tried referencing with formula in a different column but am unable to figure it out. 

Paul Newcome

I would suggest building out a quick table with the month long form in one column and the month number in the next column like so:

January          1

February         2

March             3

April                4

..........

.

To pull the month, we would use:

 

=INDEX([Month Number]:[Month Number], MATCH(LEFT([Date Column]@row, FIND(" ", [Date Column]@row) - 1), [Month Longform]:[Month Longform], 0))

 

The day would be:

 

=VALUE(MID([Date Column]@row, FIND(" ", [Date Column]@row) + 1, FIND(" ", [Date Column]@row, FIND(" ", Date Column]@row) + 1) - (FIND(" ", [Date Column]@row) + 1)))

 

And the year would be:

 

=VALUE(RIGHT([Date Column]@row, 4))

.

We would then drop them into place in a DATE function.

=DATE(yyyy, mm, dd)

 

=DATE(VALUE(RIGHT([Date Column]@row, 4)), INDEX([Month Number]:[Month Number], MATCH(LEFT([Date Column]@row, FIND(" ", [Date Column]@row) - 1), [Month Longform]:[Month Longform], 0)), VALUE(MID([Date Column]@row, FIND(" ", [Date Column]@row) + 1, FIND(" ", [Date Column]@row, FIND(" ", Date Column]@row) + 1) - (FIND(" ", [Date Column]@row) + 1))))

Lindsay Amys-Roe

Thanks, Paul! I was able to get the functions to work and tested individually. I am having issues when all put together. I keep getting #Invalid Operation.

Paul Newcome

Are you getting a specific error?

Paul Newcome

Taking a closer look, I missed an opening square bracket in the third FIND function.

 

=VALUE(MID([Date Column]@row, FIND(" ", [Date Column]@row) + 1, FIND(" ", [Date Column]@row, FIND(" ", [Date Column]@row) + 1) - (FIND(" ", [Date Column]@row) + 1)))

 

Incorporating that into the DATE formula:

 

=DATE(VALUE(RIGHT([Date Column]@row, 4)), INDEX([Month Number]:[Month Number], MATCH(LEFT([Date Column]@row, FIND(" ", [Date Column]@row) - 1), [Month Longform]:[Month Longform], 0)), VALUE(MID([Date Column]@row, FIND(" ", [Date Column]@row) + 1, FIND(" ", [Date Column]@row, FIND(" ", [Date Column]@row) + 1) - (FIND(" ", [Date Column]@row) + 1)))

Lindsay Amys-Roe

I am very appreciative! You have saved me many days of trial and error. 

Paul Newcome

Happy to help!