Date & Time Functions

Date and time functions allow you to alter the display and use of cells with date and/or time values. 

DATE

Returns a United States style date for the given day, month, and year.

Syntax

DATE( year, month, day )

Arguments

year 4 digit year value.
month Number between 1 and 12 for the month.
day Number between 1 and 31 for the day.

Example

DATE( 1998, 7, 4 ) returns "July 04, 1998".

DATETOTEXT

Converts any date value into a specific text format.

Syntax

DATETOTEXT( date-expression, {format-text} )

Arguments

date-expression Date or value to be formatted
format-text Format for the date.  Use the following conventions.  If no formatting is given, the default is %b %d, %Y

%a - abbreviated weekday name, e.g., "Mon"
%A - full weekday name, e.g., "Monday"
%b - abbreviated month name, e.g., "Feb"
%B - full month name, e.g., "February"
%c - locale-specific date and time.
%d - day of the month as a decimal integer (01-31)
%H - the hour as a decimal integer (00-23)
%I - the hour as a decimal integer (01-12)
%j - day of the year as a decimal number (001-366)
%m - month, as a decimal number (01-12)
%M - minute, as a decimal number (00-59)
%p - locale-specific AM/PM designation for 12-hour clock
%S - second, as a decimal number (00-61)
%U - week number of the year (00-53)
%w - weekday as a decimal number (0-6, Sunday = 0)
%W - week number of the year. Week number 1 has the first Monday. Days before that are week 0.
%x - locale-specific date
%X - locale-specific time
%y - year as a decimal number (00-99).
%Y - year with century as decimal number, e.g., 1984.
%Z - time zone name or abbreviation, or none if not known.
%% - a blank space.

Example

DATETOTEXT( 0, "%B - %Y" ) returns "December - 1969".

DAY

Returns just the day (1..31) of the month for the specified date.

Syntax

DAY( date )

Arguments

date Date value to extract the day.  Use the format (year, month, day)

Example

DAY( DATE(1998, 10, 28) ) returns 28.

DAYNAME

Returns the name of the given day of the week.

Syntax

DAYNAME( day-number, {shortName} )

Arguments

day-number A value betwen 0 and 6. Sunday is day 0
short-name If TRUE the short name is returned ("Wed" instead of "Wednesday").   By default, this option is FALSE.

Example

DAYNAME( 3, TRUE ) returns "Wed".

DAYOFYEAR

Returns the day (1..366) of the year for given a date.

Syntax

DAYOFYEAR( date )

Arguments

date Date value to extract the day. Use the format (year, month, day)

Example

DAYOFYEAR( DATE(1998,7,4) ) returns 185 since July 4 is the 185th day of 1998.

HOUR

Returns the hour (0-23) of the day for a given time.

Syntax

HOUR( time )

Arguments

time Time value to extract the day. Use the format (hour, minute, seconds)

Example

HOUR( TIME(8, 15, 10) ) returns 8.

LOCALTIME

Returns the local time. If no time is specified, the current time is used. If no timezone offset is specified, the offset of current zone is used. 

Syntax

LOCALTIME( {UTC-time}, {timezone-offset} )

Arguments

UTC-time Time in UTC format. If omitted, uses the NOW() function to retrieve the localtime. Use the format (hour, minute, seconds)
timezone-offset Number of time zones to adjust the time.

Example

LOCALTIME((8,23,15),4 ) returns 12:23:15.

MINUTE

Returns the minute (0-59) for a given time.

Syntax

MINUTE( time )

Arguments

time Time value to extract the minute. Use the format (hour, minute, seconds)

Example

MINUTE( TIME(8, 15, 10) ) returns 15.

MONTH

Returns the month (1-12) for the given date.

Syntax

MONTH( date )

Arguments

date Date value to extract the month. Use the format (year, month, day)

Example

MONTH( DATE(1998, 10, 12) ) returns 10.

MONTHNAME

Returns the month (1-12) for the given date.

Syntax

MONTHNAME( month-number, {shortName} )

Arguments

month-number A value betwen 1 and 12. January is month 1.
short-name If TRUE the short name is returned ("Jab" instead of "January").   By default, this option is FALSE.

Example

MONTHNAME( 6 ) returns "June".

NOW

Returns the immediate GMT time. See also LOCALTIME

Syntax

NOW( )

Arguments

none

Example

NOW( ) returns 7/2/98 10:12 (the current GMT time)

SECOND

Returns the seconds (0-59) for a given time.

Syntax

SECOND( time )

Arguments

time Time value to extract the seconds. Use the format (hour, minute, seconds)

Example

SECOND( TIME(8, 15, 10) ) returns 10.

TIME

Returns the seconds (0-59) for a given time.

Syntax

TIME( hour, minute, second )

Arguments

hour Number for the hour (0-23)
minutes Number for the minute (0-59).
seconds Number for the seconds (0-59).

Example

TIME( 8, 15, 10 ) returns the text "3:15 PM" when formatted as a time.

TIMETOTEXT

Converts any time value into a specific text format.

Syntax

TIMETOTEXT( time-expression, {format-text} )

Arguments

date-expression Date or value to be formatted
format-text Format for the date.  Use the following conventions.  If no formatting is given, the default is %b %d, %Y

%a - abbreviated weekday name, e.g., "Mon"
%A - full weekday name, e.g., "Monday"
%b - abbreviated month name, e.g., "Feb"
%B - full month name, e.g., "February"
%c - locale-specific date and time.
%d - day of the month as a decimal integer (01-31)
%H - the hour as a decimal integer (00-23)
%I - the hour as a decimal integer (01-12)
%j - day of the year as a decimal number (001-366)
%m - month, as a decimal number (01-12)
%M - minute, as a decimal number (00-59)
%p - locale-specific AM/PM designation for 12-hour clock
%S - second, as a decimal number (00-61)
%U - week number of the year (00-53)
%w - weekday as a decimal number (0-6, Sunday = 0)
%W - week number of the year. Week number 1 has the first Monday. Days before that are week 0.
%x - locale-specific date
%X - locale-specific time
%y - year as a decimal number (00-99).
%Y - year with century as decimal number, e.g., 1984.
%Z - time zone name or abbreviation, or none if not known.
%% - a blank space.

Example

TIMETOTEXT( TIME(8,15,10) ) returns  "08:15:10".

TIMEZONE

Returns the difference in hours in minutes from UTC time.

Syntax

TIMEZONE( { timezone-name } )

Arguments

timezone-name Name of the timezone. If omitted, the function uses the current timezone.

Example

TIMEZONE( "PST" ) returns 4 if formatted as hours. There is a 4 hour difference between PST and UTC time zones.

WEEKDAY

Returns a day of the week value given a specific date.  Returns a numeric value (0-6) where Sunday is 0 and Saturday is 6.

Syntax

WEEKDAY( date )

Arguments

date Date value to extract the weekday value. Use the format (year, month, day)

Example

WEEKDAY( DATE(1998, 7, 4) ) returns 6 since July 4, 1998 falls on a Saturday.

WEEKOFYEAR

Returns a week of a year value given a specific date.  Returns a numeric value (0-53) where the first week of a year is 1, the second is 2, and so forth. 

Syntax

WEEKOFYEAR( date )

Arguments

date Date value to extract the week of the year value. Use the format (year, month, day)

Example

WEEKOFYEAR( DATE(1998, 7, 4) ) returns 626 since July 4, 1998 is in the 26th week of 1998.

YEAR

Returns the year for a given date. 

Syntax

YEAR( date )

Arguments

date Date value to extract year value. Use the format (year, month, day)

Example

YEAR( DATE(1998, 10, 12) ) returns 1998.


Copyright © 1999, Gobe Software, Inc