Version

Date Calculated Fields

Date formulas can be used to output different date and time information in your widget.

Note: Your calculated field will show the information you include in the formula, overwriting of the formatting you have set for Date and Time.

The functions included in the date category are:

Function Name Syntax and Sample

date: date will return a date set to the values you include in your formula.

Syntax: date({year},{month},{day},{hour},{minute},{second})

Sample: date(1971,11,08,12,59,08)

datevalue: datevalue converts applies a specified format to a date with no initial format (date). It then returns a date in a speicified language (locale).

Syntax: datevalue({date},{format},{locale})

Sample: datevalue("23/05/2015","dd/mm/yyyy","en")

day: day uses the date syntax, and returns the third value in the formula.

Syntax: day({date})

Sample: day(date(1971,11,08,01,22,44))

formatdate: formatdate uses the date syntax, and applies a specified format to the date. It then returns a text with a date in a specified language (locale).

Syntax: formatdate({date},{format},{locale})

Sample: formatdate(date(1971,11,08,01,22,44),"dd/mm/yyyy","en")

fquarter: fquarter uses the date syntax and a second argument, number, and returns the quarter of the fiscal year for the specified date.

Syntax: fquarter({date},{number})

Sample: fquarter(date(2017,12,1,12,33,48),4)

fyear: fyear uses the date syntax and a second argument, number, and returns the fiscal year for the specified date.

Syntax: fyear({date},{number})

Sample: fyear(date(2017,12,1,12,33,48),2)

hour: hour uses the date syntax, and returns the fourth value in the formula.

Syntax: hour({date})

Sample: hour(date(2017,12,1,12,33,48))

millisecond: millisecond uses the time syntax and returns the fourth value in the formula.

Syntax: millisecond({time})

Sample: millisecond(time(11,29,48,799))

minute: minute uses the date syntax, and returns the fifth value in the formula.

Syntax: minute({date})

Sample: minute(date(2017,12,1,12,33,48))

month: month uses the date syntax, and returns the second value in the formula.

Syntax: month({date})

Sample: month(date(2017,12,1,12,33,48))

monthname: monthname uses the date syntax, and returns the month name for a date.

Syntax: monthname({date},{locale})

Sample: monthname(date(2017,12,1,12,33,48),"en")

monthshortname: monthshortname uses the date syntax, and returns the month name in a short, three-letter, format for a date.

Syntax: monthshortname({date},{locale})

Sample: monthshortname(date(2017,12,1,12,33,48),"en")

now: now returns the current date and time.

Syntax: now()

Sample: now()

quarter: quarter uses the date syntax, and returns the quarter to which your date belongs.

Syntax: quarter({date})

Sample: quarter(date(2017,12,1,12,33,48))

second: second uses the date syntax, and returns the sixth value in the formula.

Syntax: second({date})

Sample: second(date(2017,12,1,12,33,48))

time: When you use time, Reveal will return a time set to the values you include in your formula.

Syntax: time({hour},{minute},{second},{millisecond})

Sample: time(11,08,08,11)

today: today returns the current date.

Syntax: today()

Sample: today()

weekday: weekday uses the date syntax, and returns the day of the week your date falls into.

Syntax: weekday({date})

Sample: weekday(date(2017,12,1,12,33,48))

weeknum: weeknum uses the date syntax, and returns the number of the week your date falls into within the specified year.

Syntax: weeknum({date})

Sample: weeknum(date(2017,12,1,12,33,48))

year: year uses the date syntax, and returns the first value in the formula.

Syntax: year({date})

Sample: year(date(2017,12,1,12,33,48))

Date

When you use date, Reveal will return a date set to the values you include in your formula.

Syntax

By default, you will see the following structure when you select date:

date({year},{month},{day},{hour},{minute},{second})

All values, including month, need to be expressed with numerical values.

Samples

A formula with the following structure will return "08-Nov-1971 12:59":

Function Name Year Month Day Hour Minute Second

date (…​)

1971

11

08

12

59

08

A formula with the following structure, however, will return "09-Nov-1971 03:59", because the value in "hour" exceeds 24 hours. Make sure you respect the order of the elements in the formula:

Function Name Year Month Day Hour Minute Second

date (…​)

1971

11

08

27

59

08

Time

When you use time, Reveal will return a time set to the values you include in your formula.

Syntax

By default, you will see the following structure when you select time:

time({hour},{minute},{second},{millisecond})

Samples

A formula with the following structure will return "11:08:08":

Function Name Hour Minute Second Millisecond

time (…​)

11

08

08

11

The millisecond parameter will not be included in your calculated field. However, you can include time within a different formula (millisecond) to display only that value.