Ask Search:
Matthew StraneyMatthew Straney 

Formula field: Pulling Time value out of Long Date / Time string

Hello Awesome Admins!
   I'm trying to pull out a 24 hour time value out of a long Date / Time String.  Example: 2017-07-12 17:45:00 -0700
  In this example we would want the "17:45" out of the string using a fomula field.  Our formula field is pulling 2 integers too many to the right and lacking two to the left. 

Current formula returns "45:00 -0"  
Current fomula: 
IF 
(BEGINS 
(RIGHT 
(LEFT(RIGHT(Date_Formatted__c,LEN(Date_Formatted__c)-3),LEN(Date_Formatted__c)-6) 
,8) 
," "), 
RIGHT(LEFT(RIGHT(Date_Formatted__c,LEN(Date_Formatted__c)-3),LEN(Date_Formatted__c)-6),7), 
RIGHT(LEFT(RIGHT(Date_Formatted__c,LEN(Date_Formatted__c)-3),LEN(Date_Formatted__c)-6) 
,8) 
)

User-added image
 
Best Answer chosen by Matthew Straney
Jeff MayJeff May
If the data type of the field you are working with is already a text you can remove the TEXT() in the formula I posted.

MID(aDateTime__c, 12, 5) 

All Answers

Jeff MayJeff May
How about if you simplify the formula:

MID(TEXT(aDateTime__c), 12, 5) 
Matthew StraneyMatthew Straney
Jeff, I'm getting a Syntax error " Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Text"  

Part two of this question is I want to use the 24 hour time stamp (06:00) for another formula field to populate a text field with Morning 00:00 - 06:00 , Daytime .......... and so on.  

 Its not working as a number field VALUE or Text field TEXT.  What should I do?
Jeff MayJeff May
If the data type of the field you are working with is already a text you can remove the TEXT() in the formula I posted.

MID(aDateTime__c, 12, 5) 
This was selected as the best answer
Matthew StraneyMatthew Straney
MID(aDateTime__c, 12, 5)  worked perfectly and pulled out the time frame.  One more question.  If we wanted to change it to a Number value how would we do that?  Because we want to populate a fomula field with one of 3 text values.  

I asked the question here if you want to answer there you'll get two best answers! https://success.salesforce.com/answers?id=9063A000000e1evQAA

Thanks Jeff May!
Jeff MayJeff May

Since the MID will give you a string, you could get each "part of the string" like this:

MID(aDateTime__c, 12, 2)  -- that is the hours
MID(aDateTime__c, 15, 5) -- this is the minutes

Wrap each in a VALUE() to get the numeric equivalent

VALUE(MID(aDateTime__c, 12, 5))