Description

Creates a date value from three numeric values.

Remark

This function should be used to represent a date to other functions instead of representing a date as text.

Example

Ex. Date(2012,7,4) – returns the date July 4th, 2012.

Description

Returns the sum of a date and a quantity of time.

Remark

DateAdd takes three input arguments:

1. A string representing the interval you want to add. The interval can be "yyyy" (year), "y" (days), "d" (days), "w" (weeks), "m" (months), "h" (hours), "n" (minutes), "s" (seconds), "ww" (weeks of year), or "q" (quarters).

2. A real number representing how much time you want to add to the date.

3. A date value.

Example

Ex. DateAdd('h',1,Now())

 – returns the date and time 1 hour from now.

Description

Modifies a date value to be output in a specified format.

Remark

Defaults to MM/dd/yy format if no alternative format is specified. Cells that use this function must be formatted

as Text.

Example

Ex. DateCustomFormat(Date(2018,2,24), "MM-dd-yy") – returns the string "02-24-18".

Description

Returns the amount of time between two dates as an integer.

Remark

DateDiff takes three input arguments:

1. A string representing the interval you want to subtract by. The interval can be "yyyy" (year), "d" (days), "w" (weeks), "m" (months), "h" (hours), "n" (minutes), "s" (seconds), "ww" (weeks of year), or "q" (quarters).

2. A date value that will be subtracted from the base value.

3. A base date value.

Example

Ex. DateDiff('yyyy', Date(1787,9,17), Now()) – returns the number of years since the signing of the United States’ Constitution

Ex. DateDiff('h', 14:00, 14:45) – returns 0 (zero), the number of complete hours elapsed during this 45 minute period.

Description

Converts a date represented as text (e.g. '30-jan-2008') to a date value.

Remark

Any time information in the Date_text is ignored. The ticks returned always represent a time-of-day of Midnight (in the server's local time).

If the year portion of Date_text is omitted, DateValue uses the current year on the server.

Use this function when comparing two dates.

Example

Ex. DateValue('30-jun-2011') – returns the date object 6/30/2011.

Ex. DateValue({Orders.OrderDate}) > DateValue(Today()) – compares the order date to today.

Description

Returns the day portion of a date as a whole number.

Remark

Values returned by the YEAR, MONTH and DAY functions will

be Gregorian

 Calendar values regardless of the display format for the supplied date value.

Example

Ex. Day({Appointment.Date}) – returns the day of the appointment.

Description

Returns the number of days between two dates based on a 360-day year.

Remark

Often used in accounting applications.

Date360 takes three input arguments:

1. The first date value.

2. The second date value.

3. Optional: True/False indicating to use European or American method of computation. If not included the American method is used.

Example

Ex. Day360({Appointment.Date}, Today()) -

 returns the number of days between today and the appointment date.

Description

Returns

a DATE

 value whose format is based on the session format.

Remark

Only accepts data objects, parameters, and cell references as input.

Example

Ex. GlobalDateFormat({Appointment.Date}) – returns the date of the appointment based on the session format.

Description

Returns

a DATETIME

 value whose format is based on the session format.

Remark

Only accepts data objects as input.

Example

Ex. GlobalDateTimeFormat({Appointment.Date}) – returns the date and time of the appointment based on the session format.

Description

Returns the hour of a time value ranging from 0 (12:00 AM) to 23 (11:00 PM).

Remark

Times may be entered as text strings within quotation marks or a date time value.

Example

Ex.  Hour("2:50:05 PM")

 – returns 14.

Description

Returns the Minute of a time value ranging from 0 to 59.

Remark

Times may be entered as text strings within quotation marks or a date time value.

Example

Ex.  Minute("2:50:05 PM")

 – returns 50.

Description

Returns the month portion of a date as a whole number, ranging from 1 (January) to 12 (December).

Remark

Values returned by the YEAR, MONTH and DAY functions will

be Gregorian

 Calendar values regardless of the display format for the supplied date value.

Example

Ex. Month({Appointment.Date}) – returns the month of the appointment.

Description

Returns today’s date and time (in local server time).

Remark

If embedding in other functions use Now(‘false’).

Example

Ex. Now() – returns the current date and time.
Ex. Now('false') returns the current date and time formatted mm/dd/yyyy hh:mm:ss.

Description

Returns the seconds of a time value ranging from 0 to 59.

Remark

When a time omits seconds, 0 (zero) is assumed.

Times may be entered as text strings within quotation marks or a date time value.

Example

Ex.  Second("2:50:05 PM")

 – returns 5.

Description

Returns the number of ticks in a period of hours, minutes and seconds.

Remark

This function should be used to represent a time to other functions instead of representing a time as text.

Time takes three input arguments:

1. Hours

2. Minutes

3. Seconds

Example

Ex.  Time(14,50,5)

 – returns 534050000000.

Description

Returns the time component of

a DATETIME

 input as a time object.

Remark

This function should be used to represent a time to other functions instead of representing a time as text.

NOTE. The return value of this function should be formatted as text. Cells formatted as General or Date may contain an erroneous placeholder date.

Example

Ex.  TimeFormat1({Appointment.Date})

 – returns the time component of the appointment date in the format ‘hh:mm tt’.

Description

Convert a time represented in text (i.e.,

 “HH-mm-ss”) into time values that can be passed to other functions.

Remark

Acceptable formats include "5:55 PM" and "17:55". A time separator is mandatory ("17:00" is acceptable, "1700" is not).

If AM/PM is not present AM is assumed. When specifying AM or PM, do not use periods ("A.M." or "P.M." will return an error).

NOTE. The return value of this function should be formatted as text. Cells formatted as General or Date may contain an erroneous placeholder date.

Example

Ex. TimeValue(Time(14,50,5))

 – returns the time object 14:50:05.

Description

Returns today’s date with no time component.

Remark

If embedding in other functions use Today(‘false’).

See

the Now()

 function to get today's

date with

 its time component.

Example

Ex. Today() – returns the current date.

Ex. Today('false') – returns the current date formatted as mm/dd/yyyy.

Description

Returns the year portion of a date as a whole number, ranging from 1 to 9999.

Example

Ex.  Year(Today()) –

 returns 2011.

Description

Returns the name of the month for a given date.

Remark

Accepts a full date or date formatted string, or simply an integer representing the month.

The month name is returned in the current language.

Available in version 2017.2 and later.

Example

Ex. MonthName(2) – returns "February"

Ex. MonthName("02/24/1991") – returns "February"

Description

Returns the fiscal quarter for a given date, as a number from 1 to 4.

Remark

Note:

 Administrators can customize this function for different localities.

Available in version 2017.2 and later.

Example

Ex. Quarter("02/24/1991") – returns 1

Description

Returns the fiscal quarter for a given date, as text from Q1 to Q4.

Remark

Note:

 Administrators can customize this function for different localities.

Available in version 2017.2 and later.

Example

Ex. Quarter("02/24/1991") – returns "Q1"