Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Extracting a Date from a Text String into a Date Column
I have data sent from my CRM into Smartsheet. The CRM will only export an appointment date and time together as one cell, formatted as: 2/09/2016 8:00:00 AM. I need the date to be in a separate column for filtering and formatting purposes.
I've used a MID function to separate the date from the time using this formula: =MID([Appt Date/Time]23, 1, 9).
How can I convert the text that the above formula extracts into a Date?
I tried using a Value function with the Mid function to no avail: =VALUE(MID([Appt Date/Time]23, 1, 9)).
Thanks for any help!
Comments
-
Have a look at the 'smartsheet Formula Examples' Template, it has a comprehensive list of formulas.
You could try the DATE Function DATE(year, month, day)
=IF(FIND("/", [Appt Date/Time]23) = 2, DATE(VALUE(MID([Appt Date/Time]23, 6, 4)), VALUE(MID([Appt Date/Time]23, 1, 1)), VALUE(MID([Appt Date/Time]23, 3, 2))), DATE(VALUE(MID([Appt Date/Time]23, 7, 4)), VALUE(MID([Appt Date/Time]23, 1, 2)), VALUE(MID([Appt Date/Time]23, 4, 2))))
This formula Tests you date for the Day field being 1 or 2 characters long, Then Builds a Date for either option.
Hope this helps
-
Christian - That works! Thanks for your time. Not sure I would have got there on my own.
-
Hi, Christian
Can this formula used with lookup formula?
I have similar case like that.
CRM send the format for 2/09/2016 8:00:00 AM
However we need year, date and month only. I have another column minus 21 days of column with year, date and month.
We have 1000 clients. That is why we want to use lookup formula for my office managed client and then use formula to get info of meeting /time appointment
Thanks
