Extracting a Date from a Text String into a Date Column

Michelle Kohrs

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!

Christian Wells

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

 

 

Michelle Kohrs

Christian - That works! Thanks for your time. Not sure I would have got there on my own.

Peter Smith

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