+ Start a Discussion
Linda 98Linda 98 

Formula datetime help

We are using professional org.
Having a process builder which updates a field on some condition with formula type.

this is how the formula is ,i am using & between them.

I am having datetime field which is used in the update field but the format is being changed 


"Start Date:" & TEXT(DATETIMEVALUE([Opportunity].Date_Time_Start__c )) &
"End Date:" &TEXT([Opportunity].Date_Time_End__c ) &

This is the format i am looking for 
2/27/2018 4:00 PM

but this is how i am getting

2017-02-27 21:00:00Z 

How can i get this fixed?? 
 
Kailas NambiKailas Nambi
Hi,

Instead of using process builder you can use the formula field to populate value on certain condition.

Thanks,
Nambi
Alain CabonAlain Cabon
Hi,

1) DAY / MONTH / YEAR :  easy.

2) Correct hours with the right TZoffset : very tricky : https://success.salesforce.com/answers?id=9063A000000iU8pQAE

"Start Date:" &
TEXT(MONTH(DATEVALUE ([Opportunity].Date_Time_Start__c ))) &"/" &
TEXT(DAY(DATEVALUE ([Opportunity].Date_Time_Start__c )))&"/" &
TEXT(YEAR(DATEVALUE ([Opportunity].Date_Time_Start__c )))

https://help.salesforce.com/articleView?id=formula_using_date_datetime.htm&type=5

In fact, the date times (always GMT values) should be used directly without this formatting into formula as far as possible (just Visualforce Date formatting).
Alain CabonAlain Cabon
Nothing for the AM/PM by default. 20:14 (easy) instead of 08:14 PM but these values AM/PM can be inferred from the hour complicating the formula.

https://help.salesforce.com/articleView?id=custom_field_time_overview.htm

"Start Date:" & 
TEXT(MONTH(DATEVALUE ( CreatedDate ))) &"/" & 
TEXT(DAY(DATEVALUE ( CreatedDate )))&"/" & 
TEXT(YEAR(DATEVALUE ( CreatedDate ))) & " " & 
TEXT(HOUR(TIMEVALUE(CreatedDate ))) &":"& 
TEXT(MINUTE(TIMEVALUE(CreatedDate )))

Result:
Start Date:3/25/2018 20:14