Date and time functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Date and time

DATE function

This article describes the formula syntax and usage of the DATE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The DATE function returns the sequential serial number that represents a particular date. For example, the formula

=DATE(2008,7,8)

returns 39637, the serial number that represents 7/8/2008.

 Note    If the cell format was General before the function was entered, the result is formatted as a date instead of a number. If you want to view the serial number, or if you want to change the formatting of the date, select a different number format in the Number group of the Home tab.

The DATE function is most useful in situations where the year, month, and day are supplied by formulas or cell references. For example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD. You can use the DATE function in conjunction with other functions to convert the dates to a serial number that Excel recognizes. See the table in the Example section of this article for more information.

Syntax

DATE(year, month, day)

The DATE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• year  Required. The value of the year argument can include one to four digits. Excel interprets the year argument according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system; Microsoft Excel for the Macintosh uses the 1904 date system.

Tip  We recommend using four digits for the year argument to prevent unwanted results. For example, using "07" returns "1907" as the year value.

How does Excel for Windows work with the 1900 date system?

• If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example,

DATE(108,1,2)

returns January 2, 2008 (1900+108).

• If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example,

DATE(2008,1,2)

returns January 2, 2008.

• If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

How does Excel for the Macintosh work with the 1904 date system?

• If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example,

DATE(108,1,2)

returns January 2, 2008 (1900+108).

• If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example,

DATE(2008,1,2)

returns January 2, 2008.

• If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), Excel returns the #NUM! error value.
• month  Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December).
  • If month is greater than 12, month adds that number of months to the first month in the year specified. For example,

DATE(2008,14,2)

returns the serial number representing February 2, 2009.

• If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example,

DATE(2008,-3,2)

returns the serial number representing September 2, 2007.

• day  Required. A positive or negative integer representing the day of the month from 1 to 31.
  • If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example,

DATE(2008,1,35)

returns the serial number representing February 4, 2008.

• If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example,

DATE(2008,1,-15)

returns the serial number representing December 16, 2007.

 Notes 

 Note    Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

For more information, see Change the date system, format, or two-digit year interpretation.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a serial number, select the cell and then, on the Sheet tab, in the Number group, click the arrow next to Number Format, and then click Number.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I turn off automatic date function in XL?

See Also

• Add or subtract dates
• Date and time functions
• DATEVALUE function
• DAY function
• Display numbers as dates or times
• EDATE function
• MONTH function
• NOW function
• TODAY function
• YEAR function

Excel > Function reference > Date and time

DATEVALUE function

This article describes the formula syntax and usage of the DATEVALUE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008.

 Note    The serial number returned by the DATEVALUE function can vary from the preceding example, depending on your computer's system date settings.

The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations.

To view a date serial number as a date, you must apply a date format to the cell. Find links to more information about displaying numbers as dates in the See Also section.

Syntax

DATEVALUE(date_text)

The DATEVALUE function syntax has the following argument:

• date_text  Required. Text that represents a date in an Excel date format, or a reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) to a cell that contains text that represents a date in an Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999. Using the default date system in Excel for the Macintosh, the date_text argument must represent a date between January 1, 1904 and December 31, 9999. The DATEVALUE function returns the #VALUE! error value if the value of the date_text argument falls outside of this range.

If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer's built-in clock. Time information in the date_text argument is ignored.

Remarks

• Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

For more information, see Change the date system, format, or two-digit year interpretation.

• Most functions automatically convert date values to serial numbers.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the serial number as a date, select the cell, and then on the Home tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

See Also

• Change the date system, format, or two-digit year interpretation
• Convert dates stored as text to dates
• Date and time functions
• DATE function
• Display numbers as dates or times
• Insert Julian dates
• NOW function
• TIMEVALUE function
• TODAY function

Excel > Function reference > Date and time

DAY function

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax

DAY(serial_number)

Serial_number   is the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri (Hijri calendar: The lunar calendar that is used in Islamic regions.), the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• Display numbers as dates or times
• HOUR function
• MINUTE function
• MONTH function
• NOW function
• SECOND function
• TODAY function
• YEAR function

Excel > Function reference > Date and time

DAYS360 function

This article describes the formula syntax and usage of the DAYS360 function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Syntax

DAYS360(start_date, end_date, [method])

The DAYS360 function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• start_date, end_date  Required. The two dates between which you want to know the number of days. If start_date occurs after end_date, the DAYS360 function returns a negative number. Dates should be entered by using the DATE function, or derived from the results of other formulas or functions. For example, use DATE(2008,5,23) to return the 23rd day of May, 2008. Problems can occur if dates are entered as text.

For more information, see Change the date system, format, or two-digit year interpretation.

• method  Optional. A logical value that specifies whether to use the U.S. or European method in the calculation.

 Note    Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

For more information, see Change the date system, format, or two-digit year interpretation.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Date and time functions
• DAY function
• EDATE function
• EOMONTH function
• NETWORKDAYS function
• WORKDAY function

Excel > Function reference > Date and time

EDATE function

Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Syntax

EDATE(start_date,months)

Start_date   is a date that represents the start date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Months   is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• If start_date is not a valid date, EDATE returns the #VALUE! error value.
• If months is not an integer, it is truncated.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

See Also

• Date and time functions
• DATE function
• Display numbers as dates or times
• EOMONTH function
• NETWORKDAYS function
• WORKDAY function

Excel > Function reference > Date and time

EOMONTH function

Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Syntax

EOMONTH(start_date,months)

Start_date   is a date that represents the starting date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Months   is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

• If months is not an integer, it is truncated.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• If start_date is not a valid date, EOMONTH returns the #NUM! error value.
• If start_date plus months yields an invalid date, EOMONTH returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

See Also

• Date and time functions
• DATE function
• DAYS360 function
• Display numbers as dates or times
• EDATE function
• NETWORKDAYS function
• WORKDAY function

Excel > Function reference > Date and time

HOUR function

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax

HOUR(serial_number)

Serial_number   is the time that contains the hour you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remark

Microsoft Excel for Windows and Excel for the Macintosh use different date systems as their defaults. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Calculate the difference between two times
• Date and time functions
• DAY function
• Display numbers as dates or times
• MINUTE function
• MONTH function
• NOW function
• SECOND function
• WEEKDAY function
• YEAR function

Excel > Function reference > Date and time

MINUTE function

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Syntax

MINUTE(serial_number)

Serial_number   is the time that contains the minute you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remarks

Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5, since it is half of a day).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add or subtract units of time
• Calculate the difference between two times
• DAY function
• Display numbers as dates or times
• HOUR function
• MONTH function
• NOW function
• SECOND function
• WEEKDAY function
• YEAR function

Excel > Function reference > Date and time

MONTH function

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Syntax

MONTH(serial_number)

Serial_number   is the date of the month you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri (Hijri calendar: The lunar calendar that is used in Islamic regions.), the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• DAY function
• Display numbers as dates or times
• EOMONTH function
• HOUR function
• MINUTE function
• NOW function
• WEEKDAY function
• YEAR function

Excel > Function reference > Date and time

NETWORKDAYS function

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Start_date   is a date that represents the start date.

End_date   is a date that represents the end date.

Holidays   is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To convert the range of cells used for holidays in the last example into an array constant, select reference A4:A6 in the formula and then press F9.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• Date and time functions
• DATE function
• EDATE function
• EOMONTH function
• NOW function
• WEEKDAY function
• WORKDAY function

Excel > Function reference > Date and time

NOW function

This article describes the formula syntax and usage of the NOW function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Find links to more information about working with dates and times in the See Also section.

Description

Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format to the same date and time format that is specified in the regional date and time settings in Control Panel. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.

The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

 Note    If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. For more information, see the article Change formula recalculation, iteration, or precision.

Syntax

NOW()

The NOW function syntax has no arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.).

Remarks

• Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

• Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number 0.5 represents the time 12:00 noon.
• The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

See Also

• Add or subtract dates
• Add or subtract units of time
• Calculate the difference between two dates
• Calculate the difference between two times
• Date and time functions
• DATE function
• DAY function
• Display numbers as dates or times
• Insert the current date and time in a cell
• TODAY function

Excel > Function reference > Date and time

SECOND function

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Syntax

SECOND(serial_number)

Serial_number   is the time that contains the seconds you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remark

Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add or subtract units of time
• Calculate the difference between two times
• Convert times
• DATE function
• Display numbers as dates or times
• HOUR function
• Insert the current date and time in a cell
• MINUTE function
• NOW function
• TODAY function

Excel > Function reference > Date and time

TIME function

Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date.

The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax

TIME(hour,minute,second)

Hour   is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

Minute   is a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.

Second   is a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM

Remark

Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a time, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Time.

See Also

• Add or subtract units of time
• Calculate the difference between two times
• Convert times
• Display numbers as dates or times
• HOUR function
• Insert the current date and time in a cell
• MINUTE function
• NOW function
• SECOND function
• TIMEVALUE function

Excel > Function reference > Date and time

TIMEVALUE function

Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax

TIMEVALUE(time_text)

Time_text   is a text string that represents a time in any one of the Microsoft Excel time formats; for example, "6:45 PM" and "18:45" text strings within quotation marks that represent time.

Remarks

• Date information in time_text is ignored.
• Excel for Windows and Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a time, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Time.

See Also

• Add or subtract units of time
• Calculate the difference between two times
• DATEVALUE function
• Display numbers as dates or times
• HOUR function
• Insert the current date and time in a cell
• MINUTE function
• NOW function
• TIME function
• TODAY function

Excel > Function reference > Date and time

TODAY function

This article describes the formula syntax and usage of the TODAY function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number.

The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday:

=YEAR(TODAY())-1963

This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.

 Note    If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. For more information, see the article Change formula recalculation, iteration, or precision.

Syntax

TODAY( )

The TODAY function syntax has no arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.).

 Note    Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

For more information, see Change the date system, format, or two-digit year interpretation.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Count days before a date
• Date and time functions
• DATE function
• DAY function
• Display numbers as dates or times
• Insert Julian dates
• Insert the current date and time in a cell
• NOW function

Excel > Function reference > Date and time

WEEKDAY function

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax

WEEKDAY(serial_number,return_type)

Serial_number   is a sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type   is a number that determines the type of return value.

Remark

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    2/14/2008 is a Thursday.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• DAY function
• Display numbers as dates or times
• EDATE function
• NETWORKDAYS function
• NOW function
• TEXT function
• WEEKNUM function
• WORKDAY function

Excel > Function reference > Date and time

WEEKNUM function

Returns a number that indicates where the week falls numerically within a year.

Important   The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.

Syntax

WEEKNUM(serial_num,return_type)

Serial_num   is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type   is a number that determines on which day the week begins. The default is 1.

Remark

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    March 9, 2008 is a Sunday.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• Date and time functions
• DAY function
• DAYS360 function
• Display numbers as dates or times
• NETWORKDAYS function
• Show dates as days of the week
• WEEKDAY function
• WORKDAY function

Excel > Function reference > Date and time

WORKDAY function

Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Syntax

WORKDAY(start_date,days,holidays)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Start_date   is a date that represents the start date.

Days   is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays   is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• If any argument is not a valid date, WORKDAY returns the #VALUE! error value.
• If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.
• If days is not an integer, it is truncated.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Notes

• To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.
• To convert the range of cells used for holidays in the last example into a array constant, select A4:A6 and then press F9.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• Display numbers as dates or times
• EDATE function
• EOMONTH function
• NETWORKDAYS function
• NOW function
• Show dates as days of the week
• WEEKDAY function
• WEEKNUM function

Excel > Function reference > Date and time

YEAR function

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

Syntax

YEAR(serial_number)

Serial_number   is the date of the year you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri (Hijri calendar: The lunar calendar that is used in Islamic regions.), the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add or subtract dates
• Calculate the difference between two dates
• Date and time functions
• DAY function
• DAYS360 function
• Display numbers as dates or times
• MONTH function
• NOW function
• TODAY function
• YEARFRAC function

Excel > Function reference > Date and time

YEARFRAC function

Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Syntax

YEARFRAC(start_date,end_date,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Start_date   is a date that represents the start date.

End_date   is a date that represents the end date.

Basis   is the type of day count basis to use.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• All arguments are truncated to integers.
• If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
• If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.