Salesforce formulas to display Date in yyyyMMMdd and Date/Time in yyyyMMddHHmmss format

In this article, I will show you how to create a formula to display Date in yyyyMMMdd and Date/Time in yyyyMMddHHmmss format in Salesforce.

In Office File Creator Ver. 2, formatting of merge fields is available. For more information, please refer to Formatting of Date and Date/Time Fields in the manual.

 

Display Data Fields in yyyyMMdd

Formula to display Date field DateFld__c in yyyyMMdd

TEXT(YEAR(DateFld__c)) & LPAD(TEXT(MONTH(DateFld__c)),2,"0") & LPAD(TEXT(DAY(DateFld__c)),2,"0")

*The formula return type is text.

 

e.g., DateFld__c 2023/03/06 20230306

Record Page

 

Developer Console

 

Display Data/Time Fields in yyyyMMddHHmmss

Formula to display Date/Time field DateTimeFld__c in yyyyMMddHHmmss

LEFT(TEXT(DateTimeFld__c - (8/24)), 4) & MID(TEXT(DateTimeFld__c - (8/24)), 6, 2) & MID(TEXT(DateTimeFld__c - (8/24)), 9, 2) &

MID(TEXT(DateTimeFld__c - (8/24)), 12, 2) &

MID(TEXT(DateTimeFld__c - (8/24)), 15, 2) &

MID(TEXT(DateTimeFld__c - (8/24)), 18, 2)

*The formula return type is text.

*The 8 in *-(8/24) is the time difference between the user's time zone and GMT. Los Angeles is -8 hours, so -(8/24).

 

e.g., DateTimeFld__c 2023/03/06 5:15 PM 20230306171500

Record Page

 

Developer Console

DateTimeFld__c is displayed in the developer console at 03/07 01:15, +8 hours, because date/time entries are stored internally in GMT. In the formula DateTimeFldFml__c is correctly displayed in the user's time zone.

 

Memo

The addition or subtraction of hours for Date/Time fields is calculated using the following formula.

 

Add N hours to a date/time field: Datetimefield__c + (N/24)

Subtract N hours to a date/time field: Datetimefield__c - (N/24)

 

Reference: Salesforce Help Add or subtract hours from a Date/Time field

 

Set merged fields in file name in OFC

When outputting a file using OFC, you can set the merged fields in the output file name. As an example, the file name is output in the format Invoice_Opportunity.Name_yyyyyMMdd.

 

First, create a formula field for the Opportunity.

Formula Return Type: Text

Field Label: Invoice Output Date (yyyyMMdd)

Field Name: InvoiceOutputDateFml_yyyyMMdd__c

Formula

TEXT(YEAR(TODAY())) & LPAD(TEXT(MONTH(TODAY())),2,"0") & LPAD(TEXT(DAY(TODAY())),2,"0")

 

Set the following for "Output File Name" in the OFC_Template record.

Invoice_{!Opportunity.Name}_{!Opportunity.InvoiceOutputDateFml_yyyyMMdd__c}

 

Test output.

 

The file will be output with the file name "Invoice_Burlington Textiles Weaving Plant Generator_20230306.xlsx".

 

 

Copied title and URL