Expression Editor Functions - Date and Time Category

Returns a date that is the specified number of business days after the specified date.

Format: AddBusinessDays(Date, Days, Calendar)

Where:

• Date is an actual date or a date function.
• Days is an integer, and is the number of days to add.
• Calendar is text, and is the name or the alias1 of the calendar.

Example: AddBusinessDays(Today(), 3, "UK Calendar")

Returns the difference between two dates as an integer.

Format: DateDifference(Date1, Date2)

Where Date1 and Date2 are actual dates or date functions.

If Date1 is later than Date2, the result is a negative integer.

Converts a date value to a string using the specified date/time format.

Format: DateFormat(date[, format])

If the format code is used, it must be entered with double quotation marks. Valid format codes include:

• "%d" to return the day of the month
• "%g" to return A.D. or B.C.
• "%h" to return the hours
• "%m" to return the minutes
• "%M" to return the month
• "%s" to return the seconds
• "%y" to return the 2-digit year
• "%z" to return the time-zone abbreviation

Removes the time component from a date value.

Format: DateOnly(date)

Where date is an actual date or a date function.

Returns a date range between the specified dates 'From' and 'To'.

Format: DateRange(From, To)

Where From and To are actual dates or date functions.

Returns a date.

Format: DateTime(year, month, day[, hour[ ,minute[ ,second]]])

If hour, minute or second are omitted, their values are assumed to be 0.

Returns the day from a date as an integer in the range [1, 31].

Format: Day(date)

Where date is an actual date or a date function.

Adds a number of days to a specified date.

Format: DayAdd(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Adds a number of days to a specified date.

Format: DayOf(ReferenceDate, ValueToAdd)

Where:

• ReferenceDate is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns a date range from the specified date to one that is the specified number of days later.

Format: DayPeriod(Date, ValueToAdd)

Example: DayPeriod("1/1/2014", 21) returns 1/01/2014 12:00:00 AM | 22/01/2014 12:00:00 AM

Calculates the number of days between two dates based on a 360-day year (twelve 30-day months).

Format: Days360(dateStart, dateEnd[, method])

If method is false or is omitted, the US National Association of Securities Dealers (NASD) method of calculation is used. If true, the European method of calculation is used.

Returns the date that is the specified number of months before or after the start date.

Format: EDate(start_date, months)

Where:

• start_date is an actual date or a date function.
• months is a positive or negative integer.

Example: A due date falls on the same day of the month as the date of issue.

Returns the date for the last day of the month that is the indicated number of months before or after a start date.

Format: EOMonth(start_date, months)

Where:

• start_date is an actual date or a date function.
• months is a positive or negative integer.

Example: A due date falls on the last day of the month.

Returns the first working day of the specified date's year in the specified calendar.

Format: FirstWorkingDayOfYear(Calendar, ReferenceDate)

Where:

• Calendar is text, and is the name or the alias1 of the calendar.
• ReferenceDate is an actual date or a date function.

Example: FirstWorkingDayOfYear("AU Calendar", ContractStartDate())

Returns the hour from a date as an integer in the range [0, 23].

Format: Hour(date)

Where date is an actual date or a date function.

Returns the last working day of the specified date in the specified calendar.

Format: LastWorkingDayOfMonth(Calendar, ReferenceDate)

Where:

• Calendar is text, and is the name or the alias1 of the calendar.
• ReferenceDate is an actual date or a date function.

Example: LastWorkingDayOfMonth("US Calendar", InvoiceDate())

Returns the minute from a date as an integer in the range [0, 59].

Format: Minute(date)

Where date is an actual date or a date function.

Returns the month from a date as an integer in the range [1, 12].

Format: Month(date)

Where date is an actual date or a date function.

Adds a number of months to a specified date.

Format: MonthAdd(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Example: MonthAdd("14/09/2017", -1) returns 14/08/2017 12:00:00 AM.

Returns the first day of the month that is the specified number of months before or after the start date.

Format: MonthOf(ReferenceDate, ValueToAdd)

Where:

• ReferenceDate is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Example: MonthOf("14/09/2017", -1) returns 1/08/2017 12:00:00 AM.

Returns a date range between the specified date and the date that is the specified number of months before or after the start date.

Format: MonthPeriod(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Example: =MonthPeriod("14/09/2017", 0) returns 14/09/2017 12:00:00 AM | 13/10/2017 12:00:00 AM.

If the number of months is negative, the specified date is the second date in the returned date range.

Example: MonthPeriod("14/09/2017", -2) returns 14/07/2017 12:00:00 AM | 13/09/2017 11:59:59 PM.

Returns the current date and time.

Format: Now()

Returns a date which is so many quarters from a specified date.

Format: QuarterOf(ReferenceDate, ValueToAdd)

Where:

• ReferenceDate is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns a date range from the specified date to one which is based on the ValueToAdd variable.

Format: QuarterPeriod(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns the second from a date as an integer in the range [0, 59].

Format: Second(date)

Where date is an actual date or a date function.

Returns the third Wednesday of the month based on a specified date.

Format: ThirdWednesdayOfMonth(Date)

Where Date is an actual date or date function.

Example: ThirdWednesdayOfMonth(Today())

Returns the current date, with the time set to 12:00:00 AM.

Format: Today()

Returns the current date and time as the coordinated universal time (UTC).

Format: UtcNow()

Returns the day of the week corresponding to a date.

Format: Weekday(date[, method])

Where the method can be one of the following options:

• 1 (or omitted) returns a range of [1, 7], where 1 is Sunday.
• 2 returns a range of [1, 7], where 1 is Monday.
• 3 returns a range of [0, 6], where 0 is Monday.

Returns a date which is so many weeks from a specified date.

Format: WeekOf(ReferenceDate, ValueToAdd)

Where:

• ReferenceDate is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns a date range from the specified date to one which is based on the ValueToAdd variable.

Format: WeekPeriod(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns the year from a date as a 4-digit integer.

Format: Year(date)

Where date is an actual date or a date function.

Adds a number of years to a specified date. The number of years may be negative.

Format: YearAdd(Date, ValueToAdd)

Where:

• Date is an actual date or a date function.
• ValueToAdd is a positive or negative integer.

Returns the fraction of the year representing the number of whole days between the two specified dates.

Format: YearFrac(start_date, end_date [,basis])

Where the basis can be one of the following calculation options:

• 0 (or omitted) uses US (NASD) 30 / 360.
• 1 uses Actual / Actual.
• 2 uses Actual / 360.
• 3 uses Actual / 365.
• 4 uses European 30 / 360.

Example: YearFrac(Date("24/10/2013"), Date("12/12/2013"), 1)

Returns a date which is so many years from a specified date.

Format: YearOf(ReferenceDate, ValueToAdd)

Where:

• ReferenceDate is an actual date or a date function.
• ValueToAdd is a positive or negative integer.