Split DateTime into Date & Time Fields In Salesforce

Split DateTime into Date & Time Fields In Salesforce

Raghavendhra Yarlagadda's photo
Raghavendhra Yarlagadda
·

2 min read

Table of contents

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 learned how to split DateTime into Date & Time.
Cool one, Right?

If you like the content, give it some sharing 🙂

Salesforce