Today, I am going to show you how to convert a date/time field/value to a string format like this: mm/dd/yyyy hh:mm:ss AM/PM.

I am going to use Flow to demonstrate. Let's say {!createdDate} is a date/time field and {!timezoneOffset} is a number field. For example,

  • {!createdDate}: 2020-01-21T22:35:19.000+0000 (UTC)
  • {!timezoneOffset}: 5 / 24 = 0.208333333 (EST timezone is 5 hours behind UTC)

You can go ahead and replace {!createdDate} and {!timezoneOffset} with the field name if you are using the formula somewhere else like object field formula, Process Builder, etc.

Here's the formula:

MID(TEXT({!getCreatedDate} - {!timezoneOffset}), 6, 2) & "/" &
MID(TEXT({!getCreatedDate} - {!timezoneOffset}), 9, 2) & "/" &
MID(TEXT({!getCreatedDate} - {!timezoneOffset}), 0, 4) & " " & 
IF(
   OR( 
      VALUE(MID(TEXT({!createdDate} - {!timezoneOffset}), 12, 2)) = 0,
      VALUE(MID(TEXT({!createdDate} - {!timezoneOffset}), 12, 2)) = 12
   ), "12", 
   LPAD(TEXT(
      VALUE(MID(TEXT({!createdDate} - {!timezoneOffset}), 12, 2)) - 
      IF(VALUE(MID( TEXT({!createdDate} - {!timezoneOffset}), 12, 2 )) < 12, 0, 12)
   ) , 2, "0")
) & ":" & 
MID(TEXT({!createdDate}), 15, 2) & ":" & 
MID(TEXT({!createdDate}), 18, 2) & " " & 
IF(VALUE(MID(TEXT({!createdDate} - {!timezoneOffset}), 12, 2 )) < 12, "AM", "PM") & " EST"

There you have it! You now successfully convert a date/time 2021-01-21T22:35:19.000+0000 to a string like this: 01/21/2021 05:35:19 PM EST.

Hope this helps! See ya!

Post was published on , last updated on .

Like the content? Support the author by paypal.me!