How to add days, weeks, months, quarters, or years to a selected date using DateAdd()?

About this article

This article will explain what the DateAdd() function is and provide examples of how it can be utilized with the binding syntax in smart templates.  

• What is the DateAdd() function? 
• DateAdd() function examples

 

Prerequisites

Library and at least one Dynamics modules enabled Templafy Desktop and Office VSTO add-in installed on the PC Tenant admin/owner access or Space owner access

 

What is the DateAdd() function? 

DateAdd() is a function that calculates a date based on another date. It adds days, weeks, months, quarters, or years to the source date.

 

DateAdd() logic

Syntax	{{DateAdd (Date, Number, Text)}}
Input	DateTime, Units, UnitType
Output	Date with Units added, where Units are UnitType ("d", "M", "Q", "w", "y")

 

The values "d", "M", "Q", "w" and "y" are case sensitive.

 

The syntax below can also be used. If the 'Text' variable is omitted then the Units are interpreted as days.

Syntax	{{DateAdd (Date, Number}}
Input	DateTime, Units
Output	Date with Units added, where Units are Days ("d")

 

DateAdd() function examples

Example 1

Using DateAdd() to add 14 days/weeks/months/quarters/years based on the value of the "Date" question originating from the response form. In this example, the value of Date is "1 January 2022".

Description	Binding	Output
Date + 14 days	{{DateAdd(Form.Date, 14)}}	2022-01-15
Date + 14 days	{{DateAdd(Form.Date, 14, "d")}}	2022-01-15
Date + 14 weeks	{{DateAdd(Form.Date, 14, "w")}}	2022-04-09
Date + 14 months	{{DateAdd(Form.Date, 14, "M")}}	2023-03-01
Date + 14 quarters	{{DateAdd(Form.Date, 14, "Q")}}	2025-07-01
Date + 14 years	{{DateAdd(Form.Date, 14, "y")}}	2036-01-01

 

Example 2

Using DateAdd() function together with FormatDateTime() function to format the output.
In this example, the value of Date is "1 January 2022".

Description	Binding	Output
Date + 14 days	{{FormatDateTime(DateAdd(Form.Date, 14, "d"), "d MMMM yyyy", "en-US")}}	15 January 2022

 

Example 3

When the year is a leap year, note that it makes a difference if you use a binding 'Date + 365 days' or 'Date + 1 year'.

Current date	Binding	Output
2024-02-28	{{DateAdd(Form.Date, 365, "d")}}	2025-02-27
2024-02-28	{{DateAdd(Form.Date, 1, "y")}}	2025-02-28
2024-02-29	{{DateAdd(Form.Date, 365, "d")}}	2025-02-28
2024-02-29	{{DateAdd(Form.Date, 1, "y")}}	2025-02-28

 

Example 4

Using DateAdd() together with StringJoin() and FormatDateTime() functions to show a start and end date, separated by a "-".

Binding	{{StringJoin(" - ", FormatDateTime(Form.Date, "d MMMM yyyy", "en-US"), FormatDateTime(DateAdd(Form.Date, 1, "M"), "d MMMM yyyy", "en-US"))}}
Input	Form.Date = "1 January 2022"	Form.Date = ""
Output	1 January 2022 - 1 February 2022	Empty

 

Example 5

Using the DateAdd() function in combination with FormatDateTime() and DateValue() to show the date + 14 days as output, when the source is a HostSystem field and the formatting is 'day-month-year hours:minutes'.

Binding	{{FormatDateTime(DateAdd(DateValue(HostSystem.Date, "d-M-yyyy H:m", "nl-NL"), 14, "d"), "d MMMM yyyy")}}
Input	HostSystem date = "3-8-2020 4:30"
Output	"17 augustus 2020"

 

In this case you need to use DateValue() to avoid Templafy from interpreting '3-8' as '8 March' instead of '3 August'. For days-months always use d-M in the binding (if you use dd-M, d-MM or dd-MM then the output will be empty in some cases). For hours:minutes always use H:m in the binding (if you use e.g. HH:mm or hh:mm then the output will be empty in some cases).

 

 

Related articles

How to subtract days, weeks, months, quarters, or years from a selected date using DateSubtract()? How to display the current date using Now()? How to format date and time using FormatDateTime()? What is the binding syntax used in smart templates? What are functions, conditions, and operators? What are smart fields? What are template settings and properties? How to adjust the visibility of smart fields?