Personalise emails with a formatted date in Account Engagement

One of the benefits of using Account Engagement (Pardot) is the capability to personalise emails. We can use data from a date field to show when something has happened or is happening, for example; your renewal is coming up on DD/MM/YYYY, or your booking is on DD/MM/YYYY.

However, the formatting of this date field isn’t particularly user-friendly. How can we get the date field in our emails but also formatted in an easily readable way?

When creating new fields in Account Engagement or Sales Cloud, you have the option to define what field type it will be. Following the field creation, you can map them to use them in various ways that are important to your business i.e. dynamic lists, email communications, Salesforce reporting and much more. Below are the recommended field type mapping between both platforms:

ICYMI, the default date field when used will always display as YYYY/MM/DD in Account Engagement or YYYY/MM/DD in Sales Cloud (but this presentation depends on your user’s locale setting i.e. In the UK we’ll see it as DD/MM/YYYY but in the US they’ll see the same date as MM/DD/YYYY).

Now this can be frustrating when wanting to use the date field in your communications in Account Engagement (using either HML or the legacy variable tag) and it does not align with your brand guidelines.

Fortunately, there are ways around this and you only will need to do two things:

1. Create a text field on the Contact Object in Salesforce and map to a corresponding field in Account Engagement
2. Modify specific records in Salesforce with an automated trigger using Apex (this can be done in flow as well but for the purpose of this blog, we will stick to Apex)

1. Create a new text field

When you create a custom text field, you can display the text in whatever format you wish to have. This includes using numbers and characters on that same field.

N.B. For the different ways to display a date, reference what is available on Microsoft Excel/Google Sheets.

Once you decide on the new format for your date field, create a copy of the original field but change the type to a text field and this can remain blank for now.

2. Modify specific records in Sales Cloud with an automated trigger

For this part, you will need a developer or someone who is experienced in Apex but we have provided an example code below.

Let’s use a Sales Cloud and Account Engagement example for a date where we want to populate Booking_Date_Formatted__c with the date from Booking_Date__c on the Contact object.

First, you need a trigger on before insert and update of Contact (best practice is to have a single trigger per object so you’d combine this with any existing trigger logic you have on Contact):

trigger ContactTrigger on Contact (before insert, before update) {
switch on Trigger.operationType {
when BEFORE_INSERT {
ContactTriggerHandler.formatBookingDate(new Map<Id, Contact>(), Trigger.new);
}
when BEFORE_UPDATE {
ContactTriggerHandler.formatBookingDate(Trigger.oldMap, Trigger.new);
}
}
}

public without sharing class ContactTriggerHandler {
public static void formatBookingDate(Map<Id, Contact> oldMap, List<Contact> newList) {
Contact oldContact = oldMap.get(newContact?.Id);
if(oldContact?.Booking_Date__c != newContact.Booking_Date__c)
newContact.Booking_Date_Formatted__c = ((Datetime) newContact.Booking_Date__c)?.format('d MMMM YYYY');
}}}
}