[SalesForce] Converting a text field to date or datetime

I am trying to convert a text field that stores a value in datetime value into a formula field of date or datetime so that I can run reports off the formula field.

Text Field:

Last Login: 12/30/2012 8:54:07 PM

I have tried a formula field as date and date/time using a formula like date(), datevalue(), datetimevalue() and while they will save, the only thing I can see in the field for reports is #Error!

I tried doing some more complex string extraction and it still didn't help.

DateValue(LEFT(Last_Login__c, FIND("/", Last_Login__c)-1) & MID(
    Last_Login__c , 
    FIND("/", Last_Login__c ) + 1, 
    FIND("/", RIGHT(Last_Login__c , LEN(Last_Login__c ) - FIND("/", Last_Login__c )))
)
& 
MID(
    Last_Login__c , 
    FIND("/", Last_Login__c ) + 1, 
    FIND(" ", RIGHT(Last_Login__c , LEN(Last_Login__c ) - FIND("/", Last_Login__c )))
))

What am I doing wrong?

Best Answer

Try with this formula:

DATETIMEVALUE( 

  /* get the year */
  RIGHT(LEFT(Last_Login__c , FIND(" ", Last_Login__c ) - 1), 4)

  & "-" & 

  /* get the month */
  LEFT(Last_Login__c , FIND("/", Last_Login__c ) - 1)

  & "-" & 

  /* get the day */
  LEFT(
      MID(Last_Login__c, FIND("/", Last_Login__c ) + 1, 4), 
      FIND("/", MID(Last_Login__c , FIND("/", Last_Login__c ) + 1, 4)) - 1 
  ) 

  & " " & 

  /* get the Time */
  MID(Last_Login__c , 
     FIND(" ", Last_Login__c ), 
     LEN(Last_Login__c ) - FIND(" ",Last_Login__c ) + 1
  )
)

This, essentially, resolves to: DATETIMEVALUE("2012-12-30 8:54:07 PM")

A free tip - I used another TEXT formula field for debugging purposes so I could make sure that the formulas inside the above DATETIMEVALUE(..) resolved to a valid format. Otherwise, all I could see was the #Error! message in my Date/Time formula, and that wasn't particularly helpful.

Related Topic