What is MONTH function in Excel? MONTH function is one of Date and Time functions in Microsoft Excel that 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 of MONTH function MONTH(serial_number) The MONTH function syntax has the following arguments: Serial_number Required. The date of the month…
Tag: MONTH function
How to determine year is a leap year in Excel
If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions. Formula =MONTH(DATE(YEAR(date),2,29))=2 Explanation In the example shown, the formula in cell C5 is: =MONTH(DATE(YEAR(B5),2,29))=2 How the formula works The core of this formula is the DATE function, which will automatically adjust…
How to generate series of dates increment by 1 year from a single start date in Excel
If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the DAY, MONTH, YEAR, and DATE functions. Formula =DATE(YEAR(date)+1,MONTH(date),DAY(date)) Explanation In the example, B6 is the hard-coded start date and the formula in B7 is: =DATE(YEAR(B6)+1,MONTH(B6),DAY(B6)) To solve this…
Steps to create Dynamic calendar grid in Excel
This tutorial show how to create Dynamic calendar grid in Excel using the example below. You can set up dynamic calendar grid on an Excel worksheet with a series of formulas, as explained in this article. Explanation of how this formula works In the example shown, the formula in B6 is: =start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6) where “start” is the named range K5, and contains the date…
Count birthdays by month in Excel
This tutorial show how to Count birthdays by month in Excel using the example below. To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. Formula =SUMPRODUCT(–(MONTH(birthday)=number)) Explanation of how this formula works In the example shown, E5 contains this formula: =SUMPRODUCT(–(MONTH(birthday)=D5)) This formula counts birthdays in January (since D5…
Display Date is same month in Excel
This tutorial show how to Display Date is same month in Excel using the example below. To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. Formula =MONTH(date1)=MONTH(date2) Explanation of how this formula works In the example shown, the formula in cell D6 is: =MONTH(B6)=MONTH(C6) In…
Check If Two Dates are same month in Excel
This tutorial shows how to compare dates in Excel. To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. Formula =MONTH(date1)=MONTH(date2) Explanation In the example shown, the formula in cell D6 is: =MONTH(B6)=MONTH(C6) How the formula works In this case, Excel extracts the month from…
Get month from date in Excel
If you need to extract the month from a date, you can use the MONTH function. Note: The date in the formula below must be in a form that Excel recognizes as a valid date. Formula =MONTH(date) Explanation The MONTH function takes just one argument, the date from which you want to extract the month. In the example, the formula…
Get fiscal quarter from date in Excel
This tutorial show how to Get fiscal quarter from date in Excel. If you want to calculate the fiscal quarter from a date, and the fiscal quarter starts in a month other than January, you can use a formula based on the CHOOSE function. Formula =CHOOSE(MONTH(date),1,1,1,2,2,2,3,3,3,4,4,4) > Explanation Note: if you just need to calculate a “normal” quarter based on a…
Get fiscal year from date in Excel
To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. Formula =YEAR(date)+(MONTH(date)>=startmonth) Explanation In the example shown, the formula in D5 is: =YEAR(B5)+(MONTH(B5)>=C5) How this formula works By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then…
Add years to date in Excel
If you need to add years to date in Excel then this tutorials is for you. See the example below . To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions. Formula =DATE(YEAR(date)+years,MONTH(date),DAY(date)) Note: if you need to add an “even” multiple…
Sum by month ignore year in Excel
This tutorial shows how to Sum by month ignore year in Excel using the example below; Formula =SUMPRODUCT((MONTH(dates)=month)*amounts) Explanation To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, the formula in H6 is: =SUMPRODUCT((MONTH(dates)=3)*amounts) The result is a total of all sales in March, ignoring year. How…
How to calculate Quarter of Date in Excel
An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is in Quarter 2. 2. Let’s…