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?

0

1 Answer 1

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.

2
  • 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, 2016 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, 2016 at 12:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .