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".
