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.



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




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



Test output.


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