Chapter 4 - Dates and numbers

These eight commands calculate with dates and format dates and numbers: add time or working days to a date, measure the difference between two dates, find a weekday or the day of the year, turn a date or number into display text, and compute the internal rate of return and net present value of a series of cash flows. Chapter 1 explains the command syntax, dynamic parameter values and where results go; this chapter assumes you know it.

The examples use this main data source:

my:myFields
  my:StartDate          (date)
  my:EndDate            (date)
  my:SubmittedOn        (dateTime)
  my:DueDate            (date)
  my:Amount             (decimal)
  my:DiscountRate       (decimal, for example 0.08)
  my:CashFlowList       (text, for example -1000,300,400,500)
  my:Holidays
    my:Holiday          (date, repeating)

How the date commands read dates. DateAdd, DateDiff, GetDayOfYear and GetWeekDay expect the value as it is stored in a date or dateTime field: YYYY-MM-DD, or YYYY-MM-DDThh:mm:ss (seconds and fractions of a second are optional). They do no time zone conversion: the date and time are used exactly as written, a Z at the end is ignored, and daylight saving changes have no effect on the arithmetic. Use values without a time zone offset. A display value such as 10/9/2026 is not a valid date for these commands.

4.1 DateAdd

Adds (or subtracts) seconds, minutes, hours, days, working days, weeks, months or years to the date in a field and returns the new date. Use it for due dates, reminders and deadlines, including deadlines in business days that skip weekends and holidays.

Syntax

DateAdd /xpath=<XPath> [/dsname=<data source>] [/seconds=<n>] [/minutes=<n>] [/hours=<n>] [/days=<n>] [/weekdays=<n>] [/weeks=<n>] [/months=<n>] [/years=<n>] [/xpathhol=<XPath> [/dsnamehol=<data source>]]

Result: the new date. If the field holds a date only, the result is YYYY-MM-DD; if it holds a date and time, the result is YYYY-MM-DDThh:mm:ss.

Examples

DateAdd /xpath=/my:myFields/my:StartDate /days=30 /resultxpath=/my:myFields/my:DueDate
DateAdd /xpath=/my:myFields/my:SubmittedOn /hours=-8
DateAdd /xpath=/my:myFields/my:StartDate /weekdays=5 /xpathhol=/my:myFields/my:Holidays/my:Holiday
DateAdd /xpath=/my:myFields/my:StartDate /weekdays=10 /dsnamehol=CompanyHolidays /xpathhol=/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:HolidayDate

The first example writes a due date 30 days after the start date. The third returns the date five working days after the start date, skipping weekends and the dates in the form's holiday table; the fourth does the same with a holiday list from a SharePoint list data source named CompanyHolidays.

Notes

• Several parts can be combined in one command. They are applied in a fixed order, whatever the order you write them in: seconds, minutes, hours, days, weekdays, weeks, months, years.
• Months and years can overflow into the next month. Adding one month to 2026-01-31 gives 2026-03-03, because February 31 does not exist; adding one year to 2024-02-29 gives 2025-03-01.
• /weekdays moves one day at a time and counts only Monday to Friday days that are not holidays; the start date itself is not counted. From Friday 2026-10-09, /weekdays=1 gives Monday 2026-10-12, and /weekdays=-1 gives Thursday 2026-10-08.
• Holidays are matched by date only. A holiday is recognized only when the starting value has no time of day (a date-only field, or a time of 00:00:00); starting from a dateTime with any other time, holidays are not skipped.
• Every node selected by /xpathhol must contain a valid date. An empty holiday field makes the command fail. If /xpathhol selects no node at all (for example an empty holiday table), the command fails with Holidays node could not be selected.
• Use whole numbers. A decimal value is not rejected: for most parts the fraction is dropped, and with /weekdays a decimal can stop the command from finishing.
• The command returns the new value but does not write it anywhere. Use /resultxpath (Chapter 1) to put it in a field; it can be the same field as /xpath.
• Errors: Failed to select the field specified by the XPath., You must specify at least one part to add to the date., If /xpathhol is provided, the weekdays parameter must be used., The field does not contain a valid Date or DateTime value. (also when the field is empty). A value that is not a number fails with The value specified for Hours is not a valid whole number value. (and the same text with Seconds, Minutes, Days, Weeks, Months or Years). For /weekdays the message also says Days.

Related: DateDiff, GetWeekDay, SetValue.

4.2 DateDiff

Calculates the difference between two dates: in whole days, in working days (with optional holidays), as one part (hours, minutes, ...) of the difference, or as a total in days, hours, minutes, seconds or milliseconds.

Syntax

DateDiff /date1=<date> /date2=<date> [/component=<component>] [/xpathhol=<XPath> [/dsnamehol=<data source>]]

Result: a number. It is positive when /date1 is later than /date2.

Examples

DateDiff /date1-xpath=/my:myFields/my:EndDate /date2-xpath=/my:myFields/my:StartDate /component=days
DateDiff /date1-xpath=/my:myFields/my:EndDate /date2-xpath=/my:myFields/my:StartDate /component=weekdays /xpathhol=/my:myFields/my:Holidays/my:Holiday
DateDiff /date1=2026-10-09T17:30:00 /date2=2026-10-08T09:00:00 /component=totalhours

The first returns the number of calendar days between the two dates; the second the number of working days, excluding the holidays in the form. The third returns 32.5; with /component=days, hours and minutes the same two values return 1, 8 and 30.

Notes

• If /component is omitted, the command counts weekdays, not calendar days. Always give /component explicitly.
• Put the later date in /date1 to get a positive number. The Command Builder calls /date1 "the start date"; the behavior above is what the engine does.
• weekdays counts the days from /date2 up to, but not including, /date1, skipping Saturdays, Sundays and holidays. Monday 2026-10-05 to Friday 2026-10-09 gives 4. With dateTime values only complete 24-hour periods are counted, so use date-only values for weekday counts.
• Holidays work as in DateAdd: matched by date only, every holiday field must hold a valid date, and an empty holiday list fails with Holidays node could not be selected. A holiday on a weekend has no effect.
• hours, minutes, seconds and milliseconds return one part of the difference, like a clock, not a total. For a negative difference they wrap around (minus one hour gives 23). Use the total... components for negative differences or totals.
• days rounds down, so a difference of minus half a day gives -1. totaldays gives -0.5.
• The component name is not case-sensitive, except with /xpathhol: then it must be written weekdays in lower case, or the command fails with If /xpathhol is provided, the weekdays component must be used.
• A blank or invalid date fails with The first date is invalid. or The second date is invalid. When the dates come from fields that may be empty, run the command only when both have a value. An unknown component fails with The component is invalid.

Related: DateAdd, GetDayOfYear.

4.3 FormatDate

Turns a date into display text, such as October 9, 2026 or 09-Oct-2026, for use in a text field, a file name or an e-mail. It uses .NET standard and custom date and time format strings.

Syntax

FormatDate /date=<date> [/format=<format string>]

Result: the formatted text. Success is false if the date cannot be formatted.

Examples

FormatDate /date-xpath=/my:myFields/my:StartDate /format=MMMM d, yyyy
FormatDate /date-xpath=/my:myFields/my:SubmittedOn /format=yyyyMMdd-HHmm
FormatDate /date-xpath=/my:myFields/my:StartDate /format=dddd

For a start date of 2026-10-09, the first returns October 9, 2026 and the third Friday (with English month and day names). The second builds a sortable stamp such as 20261009-1730 for a file name.

Notes

• Common custom format tokens: yyyy year, MM month number, MMM / MMMM short / full month name, dd day, ddd / dddd short / full day name, HH hour (24-hour), hh hour (12-hour), mm minutes, ss seconds, tt AM/PM. Tokens are case-sensitive: MM is the month and mm the minutes.
• Standard formats (d, D, t, g, and so on) and month and day names follow a culture's conventions. FormatDate has no culture parameter, so for a fixed layout use a custom format.
• Pass the date as the field stores it (YYYY-MM-DD or YYYY-MM-DDThh:mm:ss).
• The formatting is done by Qdabra Forms, not by the form's own rules. If it fails, the Error field holds the service's message, or Unable to format date.

Related: FormatNumber, DateAdd.

4.4 FormatNumber

Turns a number into display text with a .NET standard or custom numeric format: thousands separators, a fixed number of decimals, currency, percent, leading zeros. Optionally, a culture sets the separators and currency symbol.

Syntax

FormatNumber /value=<number> /format=<format string> [/culture=<culture name>]

Result: the formatted text. Success is false if the number cannot be formatted.

Examples

FormatNumber /value-xpath=/my:myFields/my:Amount /format=N2
FormatNumber /value-xpath=/my:myFields/my:Amount /format=C /culture=en-US
FormatNumber /value-xpath=/my:myFields/my:Amount /format=N2 /culture=de-DE
FormatNumber /value=0.0825 /format=P2

For an amount of 1234.5, the first returns 1,234.50, the second $1,234.50 and the third 1.234,50. The last returns 8.25 % or 8.25%, depending on the culture.

Notes

• The value must be a plain number with a dot as the decimal separator, the way a number field stores it. Only the leading numeric part is read: 1,234.50 is read as 1, and 12 kg as 12. A value that does not start with a number, such as $12, fails with Value '$12' for parameter 'value' is not a valid number.
• Common formats: N2 thousands separators and two decimals; F2 two decimals without separators; C currency; P percent (the value is multiplied by 100); D6 or 000000 pad a whole number with leading zeros; #,##0.00 a custom pattern.
• The formatting is done by Qdabra Forms. If it fails, the Error field holds the service's message, or Unable to format number.
• The result is text. Write it to a text field, not back into the number field.

Related: FormatDate.

4.5 GetDayOfYear

Returns the day number within the year (1 to 366) for a date, or for today. Useful for sequence numbers, Julian-style date codes and lot numbers.

Syntax

GetDayOfYear [/date=<date>]

Result: the day number, from 1 for January 1.

Examples

GetDayOfYear
GetDayOfYear /date-xpath=/my:myFields/my:StartDate

The first returns today's day number. For a start date of 2026-10-09, the second returns 282; 2024-12-31 returns 366.

Notes

• Any time of day in the value is ignored.
• A /date that is present but empty fails with /date cannot be blank. This includes /date-xpath= pointing to an empty field: the command does not fall back to today. Leave the parameter out for today.
• An invalid date fails with Failed to get day of year for date. followed directly by Invalid datetime value: and the value.

Related: GetWeekDay, DateDiff.

4.6 GetWeekDay

Returns the date of a given weekday in the same week as a date. Weeks run from Sunday to Saturday. Use it to find the week-start (Sunday or Monday) or week-ending (Friday or Saturday) date for a timesheet or weekly report.

Syntax

GetWeekDay /day=<day> [/date=<date>]

Result: the date of that weekday, as YYYY-MM-DD.

Examples

GetWeekDay /day=su /date-xpath=/my:myFields/my:StartDate
GetWeekDay /day=saturday /date-xpath=/my:myFields/my:StartDate /resultxpath=/my:myFields/my:DueDate
GetWeekDay /day=mo

For Friday 2026-10-09, the first returns Sunday 2026-10-04 and the second Saturday 2026-10-10. The third returns the Monday of the current week.

Notes

• Because the week starts on Sunday, /day=mo for a Sunday date returns the next day: for 2026-10-04 it returns 2026-10-05. For a Monday-to-Sunday week, handle Sundays separately (for example with DateAdd /days=-6).
• The week may cross a year boundary: /day=mo for 2022-01-01 returns 2021-12-27.
• Any time of day in /date is ignored.
• Without /date, "today" is the current date in UTC, which can differ from the user's local date late in the evening or early in the morning. GetDayOfYear uses the local date instead. When it matters, pass the date explicitly.
• Errors: Invalid day parameter -- please use either a complete day name or two letter abbreviation, and Unable to parse date: followed by the value.

Related: GetDayOfYear, DateAdd.

4.7 Irr

Returns the internal rate of return for a series of periodic cash flows (payments as negative numbers, receipts as positive numbers). Use it in investment, capital request and business case forms.

Syntax

Irr /values=<n>,<n>[,<n>...] [/guess=<rate>]

Result: the internal rate of return, calculated by Qdabra Forms.

Example

Irr /values=-1000,300,400,500
Irr /values-xpath=/my:myFields/my:CashFlowList /guess=0.05

The first describes an investment of 1000 followed by three receipts; its internal rate of return is about 8.9 percent. The second reads the list from a text field.

Notes

• Write plain numbers only: no thousands separators, currency symbols or spaces inside a number. A comma always starts a new value, so -1,000 becomes the two values -1 and 000. Spaces around the commas are fine.
• An empty entry (for example a trailing comma) counts as 0.
• Give rates as decimal fractions (0.05), not percentages: /guess=5% is read as 5, which is 500 percent.
• Errors: Values must contain at least one positive and one negative number, Invalid number: followed by the entry, and Value 'abc' for parameter 'guess' is not a valid number. for a guess that is not a number.

Related: Npv, FormatNumber.

4.8 Npv

Returns the net present value of a series of periodic cash flows at a given discount rate.

Syntax

Npv /rate=<rate> /values=<n>,<n>[,<n>...]

Result: the net present value, calculated by Qdabra Forms.

Example

Npv /rate=0.08 /values=-1000,300,400,500
Npv /rate-xpath=/my:myFields/my:DiscountRate /values-xpath=/my:myFields/my:CashFlowList

The first discounts the four cash flows at 8 percent per period; the second takes the rate and the list from fields.

Notes

• /values follows the same rules as in Irr: plain numbers separated by commas, an empty entry counts as 0, and at least one negative and one positive value are required, even though a net present value could be calculated without them.
• /rate must be a number: Value 'abc' for parameter 'rate' is not a valid number. As with /guess, 8% is read as 8 (800 percent).
• To show the result as money, pass it to FormatNumber with /format=C or N2.

Related: Irr, FormatNumber.

Back to the Command Index.