2

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?

2

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.

  • I copy pasted your formula and still see #Error!. The other day I did create 3 fields to test different formulas to pull the year/month/date but when I tried combining them together I would get the #Error!. – MattC Sep 30 '16 at 12:51
  • Can you create the TEXT formula field and copy/paste my formula above but without the surrounding DATETIMEVALUE formula? What's the output that you receive then? – smukov Sep 30 '16 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.