Date and time functions

The Date Functions category works out due dates, ages, SLA deadlines and durations. Most functions take a Date and Time value and return a new date or a number. Read the time-zone section first: it explains the most common source of “off by one day” results.

Time zones

• Now returns the current time in UTC.
• Date parts and date math (Day, Month, Year, Day Of Week, the Start Of functions, the Add functions and the business-day functions) use the date exactly as given.
• A User Local column, such as createdon, is stored in UTC. So Day(createdon) returns the UTC day, which can be one day off for users far east or west of UTC. A case created at 01:30 on 5 March in Riyadh (UTC+3) was created at 22:30 on 4 March UTC, and Day(createdon) returns 4.
• When you need the local date, convert first with Convert From Utc: Day(From Utc(createdon, "Arab Standard Time")) returns 5.
• Date Only and Time Zone Independent columns are used as entered, with no conversion.

See Date and Time behaviours.

Current time

Now

NowReturns Date and Time

Returns the current date and time in UTC. It has no parameters.

Example: Add Days(Now, 7) sets a follow-up task’s due date one week from now.

Date parts

Day

Day(date)Returns Whole Number

Returns the day of the month, from 1 to 31.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The date. An empty date gives an empty result.

Example: Day(birthdate) = Day(Now) (together with Month) finds contacts whose birthday is today.

Month

Month(date)Returns Whole Number

Returns the month, from 1 (January) to 12 (December). Same parameter as Day.

Example: Month(estimatedclosedate) = 12 finds opportunities expected to close in December.

Year

Year(date)Returns Whole Number

Returns the year, such as 2026. Same parameter as Day.

Example: Year(createdon) = Year(Now) limits a check to records created this year.

Day Of Week

Day Of Week(date)Returns Whole Number

Returns the day of the week as a number: 0 = Sunday, 1 = Monday, through 6 = Saturday. Same parameter as Day.

Example: Day Of Week(scheduledstart) = 5 finds appointments scheduled on a Friday. To check working days against your organization’s calendar, use Is Business Day instead.

Ticks

Ticks(date)Returns Decimal Number

Returns a very large number that grows with time. Use it to build ordering keys or to compare two moments more precisely than in seconds. You do not need to know its unit.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The date.

Example: Concatenate("REQ-", Ticks(Now)) builds a reference that sorts in creation order.

Add to a date

The Add functions move a date forward, or backward with a negative amount. They share these rules:

• An empty date gives an empty result. An empty amount adds nothing.
• For Add Days, Add Hours, Add Minutes and Add Seconds, the amount can have a fraction, such as 1.5 days.
• The result is a Date and Time.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The starting date.
Amount	Whole Number or Floating Point Number	Yes	How many units to add. A negative amount subtracts.

Add Days

Add Days(date, days)Returns Date and Time

Example: Add Days(createdon, 30) gives a 30-day SLA deadline.

Add Hours

Add Hours(date, hours)Returns Date and Time

Example: Add Hours(Now, -24) gives the moment exactly one day ago, for “created in the last 24 hours” checks.

Add Minutes

Add Minutes(date, minutes)Returns Date and Time

Example: Add Minutes(scheduledstart, -15) sets a reminder 15 minutes before an appointment.

Add Seconds

Add Seconds(date, seconds)Returns Date and Time

Example: Add Seconds(Now, 90) sets a short retry time for an integration step.

Add Month

Add Months(date, months)Returns Date and Time

Adds one or more months. Shown as Add Months in the outline and the formula bar.

• Adding months to a day that does not exist in the target month gives the last day of that month: 31 January plus one month gives 28 or 29 February.

Example: Add Months(contractstartdate, 12) gives the renewal date of a yearly contract.

Add Years

Add Years(date, years)Returns Date and Time

Example: Add Years(birthdate, 18) ≤ Now checks that a contact is an adult.

Differences between dates

The Diff functions measure the time between two dates. They share these rules:

• The result is always positive. The order of the two dates does not matter. To know which date is earlier, compare them with Is Less Than.
• An empty date counts as Now. So Days(createdon, resolvedon) measures up to the current time while a case is not resolved.

Parameter	Type	Required	Description
Start Date	Date and Time	Yes	One date.
Target Date	Date and Time	Yes	The other date.

Days Diff

Days(start, target)Returns Whole Number

Returns the number of days between two dates. Any part of a day counts as a full day: 1 day and 2 hours gives 2. Shown as Days in the outline.

Example: Days(createdon, Now) > 5 finds cases that have been open for more than five days.

Hours Diff

Hours(start, target)Returns Floating Point Number

Returns the number of hours, rounded to one decimal: 2 hours 20 minutes gives 2.3. Shown as Hours in the outline.

Example: Hours(Case.createdon, Case.Resolution.createdon) gives a case’s resolution time.

Minutes Diff

Minutes(start, target)Returns Whole Number

Returns the number of minutes, rounded to the nearest minute. Shown as Minutes in the outline.

Example: Minutes(actualstart, actualend) gives the length of a phone call.

Months Diff

Months(start, target)Returns Whole Number

Returns the number of completed calendar months. Shown as Months in the outline.

Example: Months(Customer.createdon, Now) gives a customer’s tenure in months.

Years Diff

Years(start, target)Returns Whole Number

Returns the number of completed calendar years. Shown as Years in the outline.

Example: Years(birthdate, Now) gives a contact’s age.

Start of a period

These functions return an empty result for an empty date.

Start Of Day

Start Of Day(date)Returns Date and Time

Returns midnight at the start of the same date.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The date.

Example: createdon ≥ Start Of Day(Now) finds records created today (in UTC).

Start Of Hour

Start Of Hour(date)Returns Date and Time

Returns the start of the hour, with minutes and seconds set to 0. Same parameter as Start Of Day.

Example: Start Of Hour(scheduledstart) groups appointments into hourly slots.

Start Of Month

Start Of Month(date)Returns Date and Time

Returns midnight on the first day of the month. Same parameter as Start Of Day.

Example: IsBetween(estimatedclosedate, Start Of Month(Now), Add Days(Add Months(Start Of Month(Now), 1), -1)) finds opportunities closing this month.

Read text as a date

Parse DateTime

Parse DateTime(text, format)Returns Date and Time

Turns text into a date. Shown as Text To Date in the outline. Use it for dates that arrive as text, for example from an API payload or an imported file.

Parameter	Type	Required	Description
Text	Text	Yes	The text to read.
Format	Text	No	The expected format, such as dd/MM/yyyy or yyyy-MM-dd.

• With a format, the text must match it exactly and is read the same way everywhere. If it does not match, or you give no format, the text is read with the server’s regional settings, so a date such as 03/04/2026 may be read as day-first or month-first. Always pass a format for dates from other systems.
• Text that cannot be read as a date fails the run. When the input is not guaranteed, check it first with Match Pattern.
• Empty text gives an empty result.

Example: Parse DateTime(Payload.invoiceDate, "yyyy-MM-dd") reads an invoice date sent by an ERP system.

Convert between time zones

The time zone is picked from the Dataverse time-zone list, for example “(UTC+02:00) Amman”. Conversions use Dataverse’s own time-zone rules, including daylight saving time, and work the same in both editions.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The date to convert. An empty date gives an empty result.
Time Zone	Picked from the list	Yes	The time zone.

Convert From Utc

From Utc(date, timeZone)Returns Date and Time

Converts a UTC date to the local time of the chosen time zone. Shown as From UTC in the outline.

Example: Day(From Utc(createdon, "Arab Standard Time")) gives the day a case was created in Riyadh time.

Convert To Utc

To Utc(date, timeZone)Returns Date and Time

Converts a local time in the chosen time zone to UTC. Shown as To UTC in the outline.

Example: To Utc(Payload.appointmentTime, "Arabic Standard Time") stores an appointment time received in Baghdad local time correctly.

Convert Time Zone

To Time Zone(date, timeZone)Returns Date and Time

Converts a date into the chosen time zone.

• A time zone that is not recognized returns the date unchanged.

Example: To Time Zone(scheduledstart, "GMT Standard Time") shows an appointment in London time for an email to a UK customer.

Business days

The three business-day functions skip weekends and the holidays in your organization’s Business Closures calendar. They share these rules:

• The Work week slot is optional and defaults to Monday to Friday. Type the working days with the codes SU MO TU WE TH FR SA (case doesn’t matter), as a range or a list: SU-TH, SU,MO,TU,WE,TH or SU,MO-WE. A typo is reported in the editor.
• Dates are used as given. Convert User Local values with Convert From Utc first when the local date matters.
• Holidays come from the organization’s Business Closures calendar, so the same logic works in both editions.

Add Business Days

AddBusinessDays(date, days, workWeek)Returns Date and Time

Adds a number of working days to a date, keeping the time of day.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The starting date.
Number of working days	Whole Number	Yes	How many working days to add. A negative number goes back.
Work week (optional, e.g. SU-TH; default MO-FR)	Text	No	The working days of the week.

• 0 days returns the date unchanged, even on a weekend.
• An empty date gives an empty result.

Example: AddBusinessDays(createdon, 3, "SU-TH") gives a three-working-day SLA deadline for a team that works Sunday to Thursday.

Is Business Day

IsBusinessDay(date, workWeek)Returns Yes/No

Returns Yes when the date is a working day of the work week and not a business closure.

Parameter	Type	Required	Description
Date	Date and Time	Yes	The date to check. An empty date gives No.
Work week (optional, e.g. SU-TH; default MO-FR)	Text	No	The working days of the week.

Example: IsBusinessDay(scheduledstart) in a validation stops users from booking site visits on weekends or public holidays.

Business Days Between

BusinessDaysDif(start, end, workWeek)Returns Whole Number

Counts the working days after the start date, up to and including the end date.

Parameter	Type	Required	Description
Start date	Date and Time	Yes	Where counting starts (not counted).
End date	Date and Time	Yes	Where counting ends (counted when it is a working day).
Work week (optional, e.g. SU-TH; default MO-FR)	Text	No	The working days of the week.

• The result is negative when the end date is before the start date. Unlike the Diff functions, the order matters.
• If either date is empty, the result is empty.
• It is the reverse of Add Business Days: AddBusinessDays(start, BusinessDaysDif(start, end)) gives back end when end is a working day.

Example: BusinessDaysDif(createdon, Now) > 2 flags cases waiting more than two working days for a first response.

Related

• Data types
• Empty values
• To String: format a date as text