Need to formulate long date imported from form into a reportable date in Smartsheet
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.
Comments
-
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))))
-
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.
-
Are you getting a specific error?
-
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)))
-
I am very appreciative! You have saved me many days of trial and error.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
