Date and Time Functions

the date system you are using. By default, Excel for Windows uses the 1900 date system.For the 1900 date system:

For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

For example, DATE(2000,1,2) returns January 2, 2000.

For the 1904 date system:

For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

For example, DATE(2000,1,2) returns January 2, 2000.

Excel returns the #NUM! error value.

month adds that number of months to the first month in the year specified. For example, DATE(1998,14,2) returns the serial number representing February 2, 1999.

in the month specified, day adds that number of days to the first day in the month. For example, DATE(1998,1,35) returns the serial number representing February 4, 1998.

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. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

4/20/2015quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

yields a future date; a negative value yields a past date.

30-Jan-1998 are text strings within quotation marks that represent dates. Using the default date

DATE(year,month,day) --- Returns the serial number that represents a particular date.

Year The year argument can be one to four digits. Excel interprets the year argument according to

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

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

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

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

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

If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive),

Month is a number representing the month of the year. If month is greater than 12,

Day is a number representing the day of the month. If day is greater than the number of days

EDATE(start_date,months) --- Returns the serial number that represents the date that is the indicated number of

Start_date is a date that represents the start date. Dates may be entered as text strings within

Months is the number of months before or after start_date. A positive value for months

If start_date is not a valid date, EDATE returns the #NUM! error value.If months is not an integer, it is truncated.

DATEVALUE(date_text) --- Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.

Date_text is text that represents a date in an Excel date format. For example, "1/30/1998" or

42081 system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.

as text strings within quotation marks (for example, "2001/1/30"), as serial numbers 31 (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), 22 or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).

marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

18

10within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

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 of the serial numbers that represent the dates.

8/28/2015 14:19

date-time code used by Microsoft Excel for date and time calculations. 8/28/2015

If the year portion of date_text is omitted, DATEVALUE

DATEDIF(start_date,end_date,unit) --- Calculates the number of days, months, or years between two dates.

Start_date is a date that represents the first, or starting, date of the period. Dates may be entered .

End_date is a date that represents the last, or ending, date of the period.Unit is the type of information you want returned.

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

Serial_number is the date of the day you are trying to find. Dates may be entered as text strings within quotation

NETWORKDAYS(start_date,end_date,holidays) --- Returns the number of whole working days between start_date

Start_date is a date that represents the start date. Dates may be entered as text strings

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

NOW( ) --- Returns the serial number of the current date and time.

TODAY( ) --- Returns the serial number of the current date. The serial number is the

MONTH(serial_number) --- 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).

within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers 3 (for example, 35825, which represents January 30, 1998, if you're using the 1900 date

system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers2015 (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system),

or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

a value ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.)..

ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

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")).

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")).

Serial_number is the date of the month you are trying to find. Dates may be entered as text strings

EOMONTH(start_date,months) --- 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.

Start_date is a date that represents the starting date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).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.

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

Serial_number is the date of the year you want to find. Dates may be entered as text strings

TIME(hour,minute,second) --- Returns the decimal number for a particular time. The decimal number returned by TIME is

Hour is a number from 0 (zero) to 23 representing the hour.Minute is a number from 0 to 59 representing the minute.Second is a number from 0 to 59 representing the second.

HOUR(serial_number) --- Returns the hour of a time value. The hour is given as an integer,

Serial_number is the time that contains the hour you want to find. Times may be entered as text strings within

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

Serial_number is the time that contains the minute you want to find. Times may be entered as

DAYS360 Calculates the number of days between two dates based on a 360-day yearDAYS360(start_date,end_date,method)

METHOD:start 1/28/2008end 1/31/2008

Formula DescriptionNumber of days between the two dates above, based on a 360-day yearUsing the method true

EDATE Returns the serial number of the date that is the indicated number of months before or after the start dateEDATE(start_date,months)

1/15/2008Formula Description

The date, one month after the date aboveThe date, one month before the date aboveThe date, two months after the date above

TIMEVALUE Converts a time in the form of text to a serial numberTIMEVALUE(time_text)

Formula Description0.1 Decimal part of a day, for the time

0.2743055556 Decimal part of a day, for the time

WEEKDAY Converts a serial number to a day of the weekWEEKDAY(serial_number,return_type)

5 Return_typeData 1-SUNDAY; 2- MONDAY; 3- 0 1 or omitted1/1/2008 2

Formula Description 3Day of the week, with numbers 1 (Sunday) through 7 (Saturday)Day of the week, with numbers 1 (Monday) through 7 (Sunday)

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

Serial_number is the time that contains the seconds you want to find. Times may be

Day of the week, with numbers 0 (Monday) through 6 (Sunday)

WEEKNUM Converts a serial number to a number representing where the week falls numerically with a yearWEEKNUM(serial_num,return_type)

12 Serial_numData 19-Mar-08 2

Formula DescriptionNumber of the week in the year, with a week beginning on SundayNumber of the week in the year, with a week beginning on Monday

WORKDAY Returns the serial number of the date before or after a specified number of workdaysWORKDAY(start_date,days,holidays)

12/2/2008Date Description6/1/2008 Start date

130 Days to completion11/26/2008 Holiday12/4/2008 Holiday

11/21/2008 HolidayFormula Description

Date 130 workdays from the start date 11/28/2008)Date 130 workdays from the start date, excluding holidays

YEARFRAC Returns the year fraction representing the number of whole days between start_date and end_date

Data Description 0.25 ONE FOURTH OF THE YEAR1/1/2008 Start date

7/30/2008 End date2 Actual/360

Formula Description (Result)0.5861111111 Fraction of the year between the two dates

211/360

Note March 9, 2008 is a Sunday.

YEARFRAC(start_date,end_date,basis)

the date system you are using. By default, Excel for Windows uses the 1900 date system.

For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

month adds that number of months to the first month in the year specified. For example, DATE(1998,14,2) returns the serial number representing February 2, 1999.

in the month specified, day adds that number of days to the first day in the month. For example, DATE(1998,1,35) returns the serial number representing February 4, 1998.

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. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

yields a future date; a negative value yields a past date.

30-Jan-1998 are text strings within quotation marks that represent dates. Using the default date

--- Returns the serial number that represents a particular date.

The year argument can be one to four digits. Excel interprets the year argument according to

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

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

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

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

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

is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive),

is a number representing the month of the year. If month is greater than 12,

is a number representing the day of the month. If day is greater than the number of days

--- Returns the serial number that represents the date that is the indicated number of

is a date that represents the start date. Dates may be entered as text strings within

is the number of months before or after start_date. A positive value for months

is not a valid date, EDATE returns the #NUM! error value. is not an integer, it is truncated.

--- Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.

is text that represents a date in an Excel date format. For example, "1/30/1998" or

system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.

This function is provided for compatibility.

as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).

marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

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.

within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

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 of the serial numbers that represent the dates.

date-time code used by Microsoft Excel for date and time calculations.

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

--- Calculates the number of days, months, or years between two dates.

is a date that represents the first, or starting, date of the period. Dates may be entered .

is a date that represents the last, or ending, date of the period. is the type of information you want returned.("Y", "M", "D", "MD", "YM", "YD")

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

is the date of the day you are trying to find. Dates may be entered as text strings within quotation

--- Returns the number of whole working days between start_date

is a date that represents the start date. Dates may be entered as text strings

is a date that represents the end date. is an optional range of one or more dates to exclude from the working calendar, such

--- 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).

within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

5/31/2015

within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

a value ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.)..

ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

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")).

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")).

is the date of the month you are trying to find. Dates may be entered as text strings

--- 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.

is a date that represents the starting date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")). 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.

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

is the date of the year you want to find. Dates may be entered as text strings

) --- Returns the decimal number for a particular time. The decimal number returned by TIME is

) --- Returns the hour of a time value. The hour is given as an integer,

is the time that contains the hour you want to find. Times may be entered as text strings within

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

is the time that contains the minute you want to find. Times may be entered as

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")).

Calculates the number of days between two dates based on a 360-day year

FALSE or omitted: IF THE STARTING DATE IS 31 THEN IT WILL MOVED IT TO THE 30. U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.TRUE: IT WILL MOVE BACK OR FORWARD European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

12

Returns the serial number of the date that is the indicated number of months before or after the start date

Number returnedNumbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.

Numbers 1 (Monday) through 7 (Sunday).Numbers 0 (Monday) through 6 (Sunday).

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

is the time that contains the seconds you want to find. Times may be

Converts a serial number to a number representing where the week falls numerically with a year

Week BeginsWeek begins on Sunday. Weekdays are numbered 1 through 7.Week begins on Monday. Weekdays are numbered 1 through 7.

Returns the serial number of the date before or after a specified number of workdays

Returns the year fraction representing the number of whole days between start_date and end_date

Basis Day count basisONE FOURTH OF THE YEAR 0 or omittedUS (NASD) 30/360

1 Actual/actual2 Actual/3603 Actual/3654 European 30/360

42081.001.002.00

1/18/2015

uses the current year from your computer's built-in clock. Time information in date_text is ignored.

--- 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.

is a date that represents the starting date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

511

Day count basisUS (NASD) 30/360Actual/actualActual/360Actual/365European 30/360

is a date that represents the starting date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.

Date and time functions