Category: Excel

This article describes the formula syntax and usage of the MONTH function in Microsoft Excel.

Description

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)

The MONTH function syntax has the following arguments:

• Serial_number    Required. 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.

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, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date
15-Apr-11
Formula	Description	Result
=MONTH(A2)	Month of the date in cell A2	4

This article describes the formula syntax and usage of the MINUTE function in Microsoft Excel.

Description

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

Syntax

MINUTE(serial_number)

The MINUTE function syntax has the following arguments:

• Serial_number    Required. 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

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

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Time
12:45:00 PM
Formula	Description	Result
=MINUTE(A2)	Minute portion of the time in A2.	45

This article describes the formula syntax and usage of the ISOWEEKNUM function in Microsoft Excel.

Description

Returns number of the ISO week number of the year for a given date.

Syntax

ISOWEEKNUM(date)

The ISOWEEKNUM function syntax has the following arguments.

• Date    Required. Date is the date-time code used by Excel for date and time calculation.

Remarks

• Microsoft Excel stores dates as sequential 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.
• If the date argument is not a valid number, ISOWEEKNUM returns the #NUM! error value.
• If the date argument is not a valid date type, ISOWEEKNUM returns the #VALUE! error value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date
3/9/2012
Formula	Description	Result
=ISOWEEKNUM(A2)	Number of the week in the year that 3/9/2012 occurs, based on weeks beginning on the default, Monday (10).	10

This article describes the formula syntax and usage of the HOUR function in Microsoft Excel.

Description

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)

The HOUR function syntax has the following arguments:

• Serial_number    Required. 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

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

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Time
0.75
7/18/2011 7:45
4/21/2012
Formula	Description	Result
=HOUR(A2)	Returns 75% of 24 hours	18
=HOUR(A3)	Returns the hour portion of the date/time value.	7
=HOUR(A4)	A date with no time portion specified is considered 12:00 AM, or 0 hours.	0

This article describes the formula syntax and usage of the EOMONTH function in Microsoft Excel.

Description

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)

The EOMONTH function syntax has the following arguments:

• Start_date    Required. 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    Required. 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.Note: 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.
• 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

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date
1-Jan-11
Formula	Description	Result
=EOMONTH(A2,1)	Date of the last day of the month, one month after the date in A2.	2/28/2011
=EOMONTH(A2,-3)	Date of the last day of the month, three months before the date in A2.	10/31/2010

This article describes the formula syntax and usage of the EDATE  function in Microsoft Excel.

Description

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)

The EDATE function syntax has the following arguments:

• Start_date    Required. 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    Required. 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.
• If start_date is not a valid date, EDATE returns the #VALUE! error value.
• If months is not an integer, it is truncated.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date
15-Jan-11
Formula	Description	Result
=EDATE(A2,1)	The date, one month after the date above	15-Feb-11
=EDATE(A2,-1)	The date, one month before the date above	15-Dec-10
=EDATE(A2,2)	The date, two months after the date above	15-Mar-11

This article describes the formula syntax and usage of the DAYS360 function in Microsoft 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:

• 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.
• Method    Optional. A logical value that specifies whether to use the U.S. or European method in the calculation.

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

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.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Dates
1-Jan-11
30-Jan-11
1-Feb-11
31-Dec-11
Formula	Description	Result
=DAYS360(A3,A4)	Number of days between 1/30/2011 and 2/1/2011, based on a 360-day year.	1
=DAYS360(A2,A5)	Number of days between 1/1/2011 and 12/31/2011, based on a 360-day year.	360
=DAYS360(A2,A4)	Number of days between 1/1/2011 and 2/1/2011, based on a 360-day year.	30

This article describes the formula syntax and usage of the DAYS function in Microsoft Excel. For information about the DAY function, see DAY function.

Description

Returns the number of days between two dates.

Syntax

DAYS(end_date, start_date)

The DAYS function syntax has the following arguments.

• End_date    Required. Start_date and End_date are the two dates between which you want to know the number of days.
• Start_date    Required. Start_date and End_date are the two dates between which you want to know the number of days.

Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, Jan 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900.

Remarks

• If both date arguments are numbers, DAYS uses EndDate–StartDate to calculate the number of days in between both dates.
• If either one of the date arguments is text, that argument is treated as DATEVALUE(date_text) and returns an integer date instead of a time component.
• If date arguments are numeric values that fall outside the range of valid dates, DAYS returns the #NUM! error value.
• If date arguments are strings that cannot be parsed as valid dates, DAYS returns the #VALUE! error value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
31-DEC-2021
1-JAN-2021
Formula	Description	Result
=DAYS(“15-MAR-2021″,”1-FEB-2021”)	Finds the number of days between the end date (15-MAR-2021) and start date (1-FEB-2021). When you enter a date directly in the function, you need to enclose it in quotation marks. Result is 42.	42
=DAYS(A2,A3)	Finds the number of days between the end date in A2 and the start date in A3 (364).	364

This article describes the formula syntax and usage of the DAY function in Microsoft Excel. For information about the DAYS function, see DAYS function.

Description

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)

The DAY function syntax has the following arguments:

• Serial_number    Required. 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.

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, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Date
15-Apr-11
Formula	Description (Result)	Result
=DAY(A2)	Day of the date in cell A2 (15)	15

This article describes the formula syntax and usage of the DATEVALUE function in Microsoft 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. Remember, though, that your computer’s system date setting may cause the results of a DATEVALUE function to vary from this example

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 arguments:

• Date_text    Required. Text that represents a date in an Excel date format, or a reference 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. 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.
• Most functions automatically convert date values to serial numbers.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
11
3
2011
Formula	Description	Result
=DATEVALUE(“8/22/2011”)	Serial number of a date entered as text.	40777
=DATEVALUE(“22-MAY-2011”)	Serial number of a date entered as text.	40685
=DATEVALUE(“2011/02/23”)	Serial number of a date entered as text.	40597
=DATEVALUE(“5-JUL”)	Serial number of a date entered as text, using the 1900 date system, and assuming the computer’s built-in clock returns 2011 as the current year.	39634
=DATEVALUE(A2 & “/” & A3 & “/” & A4)	Serial number of a date created by combining the values in cells A2, A3, and A4.	40850