Split DateTime into Date & Time Fields In Salesforce

Hello There! Welcome Back πŸ™‚
Hope you are doing great πŸ˜€

Today we are going to take a look at Splitting DateTime and Date & Time Fields.

Real-Time Use-Case
Client wants to input interview date and time in a DateTime field and want you use that date and time separately.
E.g. They want to send email alert with an email template saying “Your interview is scheduled on Date(From DateTime Field) at Time(From DateTime Field with AM/PM).”
Output: If the DateTime is “1/2/2017 9:00 AM“, The sentence should be “Your interview is scheduled on 1/2/2017 at 9:00 AM

Solution
Create 2 Formula Fields:

  1. Formula(Date) – For Date
  2. Formula(Text) – For Time
Formula For Date:

DATEVALUE(DATETIME__c)

Formula For Time:
First, you need to find your Salesforce Org’s timezone. You can find it under Company Information in your Salesforce Org.
E.g. lets take it as +8:00 and divide it by 24 which gives us + 0.3333(referredΒ in the formula below)
  • 12 Hour Format
    IF(VALUE(LPAD(MID(TEXT(DateTime__c + 0.3333), 12, 5),2)) = 12 ,
    MID(TEXT(DateTime__c + 0.3333), 12, 5)+' PM',
    IF(VALUE(LPAD(MID(TEXT(DateTime__c + 0.3333), 12, 5),2)) < 12, IF(VALUE(LPAD(MID(TEXT(DateTime__c + 0.3333), 12, 5),2)) = 00, TEXT(12)+MID(TEXT(DateTime__c + 0.3333), 14, 3), IF(VALUE(LPAD(MID(TEXT(DateTime__c + 0.3333), 12, 5),2)) < 10, MID(TEXT(DateTime__c + 0.3333), 13, 4), MID(TEXT(DateTime__c + 0.3333), 12, 5)))+' AM', TEXT(VALUE(MID(TEXT(DateTime__c + 0.3333), 12, 2))-12)+MID(TEXT(DateTime__c + 0.3333), 14, 3)+' PM')
  • 24 Hour Format
    MID(TEXT(Meeting_Date_and_Time__c + 0.3333), 12, 5) + IF(VALUE(LPAD(MID(TEXT(Meeting_Date_and_Time__c + 0.3333), 12, 5),2)) < 12 , ' AM', ' PM')

That is it! We learnt how to split DateTime into Date & Time.
Cool one, Right?

If you like the content, give it some sharing πŸ™‚

Happy Coding πŸ˜€
For more Salesforce Content, Please Like & Subscribe to My Facebook Page and Youtube Channel with the links below.
Thank you, See you in the next one & Peace πŸ˜€


Stay update with our latest posts by subscribing to our newsletter. It is free!




Leave a Reply

Your email address will not be published.