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

Updated August 23, 2024 11:13

About this article

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

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

Prerequisites

Library and at least one Dynamics module 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 to

user profile general number zero placeholder tech_role

Articles in this section