Date and time functions

These functions perform calculations on date, time, or numeric expressions. Their arguments must be specified as serial numbers. The part of the number to the left of the decimal point represents the number of days since January 1, 1904 (that date being 0). The part of the number to the right of the decimal point represents the time of day. Negative numbers represent days before January 1, 1904.

Results of date and time functions are serial numbers that can be reformatted in spreadsheets with the Number command (Format menu), or in databases with the Field Info command (Edit menu).

  DATE
DATETOTEXT
DAY
DAYNAME
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW
SECOND
TEXTTODATE
TEXTTOTIME
TIME
TIMETOTEXT
WEEKDAY
WEEKOFYEAR
YEAR

DATE

Calculates a serial number for the specified date.

Format: =DATE(year,month,day)

Arguments:
year: the year (1 to 29941)
IMPORTANT: To avoid confusion when entering dates, always use 4-digit years. To make AppleWorks interpret 4-digit years properly, click "Show century" in the Date tab of the International pane of System Preferences.
month: the month of the year (1 to 12)
day: the day of the month (1 to 31)

Example: =DATE(2000,10,4) returns 35341, the serial number for October 4, 2000.

Top

DATETOTEXT

Calculates a date from the supplied serial number and format.

Format: =DATETOTEXT(serial number,format number)

Arguments:
serial number: the number of days before or after January 1, 1904
format number (optional): a number in the range 0 to 4

  This format number   Returns    
0 10/4/00 (the default)

1 Oct 4, 2000

2 October 4, 2000

3 Wed, Oct 4, 2000

4 Wednesday, October 4, 2000

Example: =DATETOTEXT(35341,1) returns Oct 4, 2000.

Top

DAY

Converts a serial number to a number for the day of the month.

Format: =DAY(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =DAY(35341) returns 4. The serial number 35341 represents the 4th day of October.

Top

DAYNAME

Converts a number to the name of the day of the week.

Format: =DAYNAME(number)

Argument:
number: a number from 1 to 7. 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

Example: =DAYNAME(6) returns Friday.

Top

DAYOFYEAR

Converts a serial number to the day of the year.

Format: =DAYOFYEAR(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =DAYOFYEAR(35341) returns 278. The serial number 35341 represents October 4, the 278th day of the year 2000.

Top

HOUR

Converts the time portion of a serial number to the number of the hour (in the range 0-23).

Format: =HOUR(serial number)

Argument:
serial number: the numbers after the decimal point of a serial number, which signify the time of day

Example: =HOUR(0.5660) returns 13. The serial number 0.5660 represents the thirteenth hour, or 1:00 P.M.

Top

MINUTE

Calculates minutes for a serial number.

Format: =MINUTE(serial number)

Argument:
serial number: the numbers after the decimal point of a serial number, which signify the time of day

Example: =MINUTE(0.5660) returns 35. The serial number 0.5660 represents the thirteenth hour and the thirty-fifth minute, or 1:35 P.M.

Top

MONTH

Converts a serial number to the number of the corresponding month.

Format: =MONTH(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =MONTH(35341) returns 10. The serial number 35341 represents October 4, the tenth month of the year 2000.

Top

MONTHNAME

Calculates the name of a month from its number.

Format: =MONTHNAME(number)

Argument:
number: a number in the range 1 to 12. The argument can also be the MONTH function, which returns the month number from the serial number.

Example: =MONTHNAME(10) returns October. =MONTHNAME(MONTH(35341)) returns October.

Top

NOW

When a spreadsheet is recalculated, this function displays the current date or time, or the serial number of the current date and time (based on the computer's clock).

Format: =NOW()

Argument: No argument

Example: If the function was last executed on October 4, 1999, =NOW() returns October 4, 1999 if the cell was formatted as a date or 34975 (the corresponding serial number) if the cell was formatted as a number.

Top

SECOND

Calculates seconds for a serial number.

Format: =SECOND(serial number)

Argument:
serial number: the numbers after the decimal point of a serial number, which signify the time of day

Example: =SECOND(0.5664) returns 37. The serial number 0.5664 represents1:35:37 P.M.

Top

TEXTTODATE

Converts a date written in text to its serial number.

Format: =TEXTTODATE(date text)

Argument:
date text: date as text expression, enclosed in quotation marks

Example: =TEXTTODATE("10/4/00") returns 35341.

Top

TEXTTOTIME

Converts a time written in text to its serial number.

Format: =TEXTTOTIME(time text)

Argument:
time text: time as text expression (hours, minutes, seconds), enclosed in quotation marks. Seconds and the A.M. and P.M. designations are optional.

Example: =TEXTTOTIME("13:30") returns 0.5625, the serial number for the time 13:30 (using a 24-hour clock).

Top

TIME

Converts a specified time (based on a 24-hour clock) into a serial number.

Format: =TIME(hour,minute,second)

Arguments:
hour: a number in the range 0 to 23 (0=midnight)
minute: a number in the range 0 to 59
second: a number in the range 0 to 59

Example: =TIME(12,0,0) returns 0.5 (12 noon is halfway through the day).

Top

TIMETOTEXT

Converts a time serial number to text.

Format: =TIMETOTEXT(serial number,format)

Arguments:
serial number: the numbers after the decimal point of a serial number, which signify the time of day
format (optional): a number in the range 0 to 3

  This format number   Returns    
0 hours:min A.M./P.M. (default; 12-hour clock)

1 hours:min:sec A.M./P.M. (12-hour clock)

2 hours:min (24-hour clock)

3 hours:min:sec (24-hour clock)

Example: =TIMETOTEXT(0.75,3) returns 18:00:00.

Top

WEEKDAY

Converts a serial number to a number for the day of the week (Sunday=1, Monday=2, and so on).

Format: =WEEKDAY(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =WEEKDAY(35341) returns 4. The serial number 35341 represents October 4, 2000, which falls on a Wednesday.

Top

WEEKOFYEAR

Converts a serial number to the number for the week in the year (from 1 to 52).

Format: =WEEKOFYEAR(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =WEEKOFYEAR(35341) returns 41. The serial number 35341 represents October 4, the forty-first week of the year 2000.

Top

YEAR

Calculates the year for a serial number.

Format: =YEAR(serial number)

Argument:
serial number: the number of days before or after January 1, 1904

Example: =YEAR(35341) returns 2000. The serial number 35341 represents October 4, 2000.

Top

Related topic

  Alphabetical list of functions

 
Table of contents Index