To generate and refer to a specific date on a Table in relevance to the current date, you can set up a Formula Field. You can generate a date (e.g. 1/1/2020), or a date with a timestamp (e.g. 1/1/2020 12:00:00 AM).


Use cases might include:


-Referencing the current date in an email.

-Referencing a coupon expiration date (e.g. 30 days after the email send date).

-Indicating a specific date a number of days ago from the current date (e.g. Trial start date).


To add a Custom Date Formula Field, follow these steps:


1. Go to Data > Tables - Click Edit on the Table you want to modify


Data>Tables


Click Edit


2. Click Add Column


Add Column


3. Name your Field (e.g. CouponExpirationDate)


Name Field


4. Select Formula as the Field type


Formula Field Type


5. In the Default Value area for the Field, enter the Formula


 To use the current date (no timestamp):

(CONVERT(nvarchar,dateadd(day,(0),getdate()),(101)))

To add 30 days to the current date (no timestamp):

(CONVERT(nvarchar,dateadd(day,(30),getdate()),(101)))

To subtract 30 days from the current date (no timestamp):

(CONVERT(nvarchar,dateadd(day,(-30),getdate()),(101)))


Enter Formula


Note: The formula structure allows you to modify the desired date, as well as to include or not include a timestamp. Here is an explanation of the logic used in the formula:

(CONVERT(nvarchar,dateadd(day,(0),getdate()),(101)))

The 0 spot selects the date:

0 = current date

Positive numbers (e.g. 30) = current date + number of days

(CONVERT(nvarchar,dateadd(day,(30),getdate()),(101)))

Negative numbers (e.g. -30) = current date - number of days

(CONVERT(nvarchar,dateadd(day,(-30),getdate()),(101)))

The 101 above removes the timestamp.

(CONVERT(nvarchar,dateadd(day,(0),getdate()),(0)))

The last 0 above includes the timestamp.

6. Save the Table


Save Table


The Table now has a Formula Field that references a specific date in relevance to the current date (with or without a timestamp).


7. Using the Custom Date Formula Field


The new Formula Field can be referenced in an ROI Segment if you would like a dynamic date in your Reports, or in Email Personalization. 


Please contact support@rightoninteractive.com for assistance.