|
![]() |
|
DATE | HOUR | NOW | WEEKDAY |
DATETOTEXT | LOCALTIME | SECOND | WEEKOFYEAR |
DAY | MINUTE | TIME | YEAR |
DAYNAME | MONTH | TIMETOTEXT | |
DAYOFYEAR | MONTHNAME | TIMEZONE |
Date and time functions allow you to alter the display and use of cells with date and/or time values.
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".
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".
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.
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".
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.
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.
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.
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.
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.
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".
Returns the immediate GMT time. See also LOCALTIME
Syntax
NOW( )
Arguments
none
Example
NOW( ) returns 7/2/98 10:12 (the current GMT time)
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.
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.
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".
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.
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.
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.
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 |