Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Perform case-sensitive Lookup in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Count rows that contain specific values in Excel
  • How to use Excel ROW Function

Data Analysis Examples

  • Number and Text Filters Examples in Excel
  • How to perform a t-Test in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Get column name from index in Excel Table
  • Conflicting Multiple Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation date in next 30 days
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation allow weekday only

Tag: MONTH function

MONTH function: Description, Usage, Syntax, Examples and Explanation

by

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

Continue Reading

How to determine year is a leap year in Excel

by

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 …

Continue Reading

How to generate series of dates increment by 1 year from a single start date in Excel

by

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 …

Continue Reading

Steps to create Dynamic calendar grid in Excel

by

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 …

Continue Reading

Count birthdays by month in Excel

by

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 …

Continue Reading

Display Date is same month in Excel

by

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 …

Continue Reading

Check If Two Dates are same month in Excel

by

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, …

Continue Reading

Get month from date in Excel

by

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

Continue Reading

Get fiscal quarter from date in Excel

by

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 …

Continue Reading

Get fiscal year from date in Excel

by

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 …

Continue Reading

Add years to date in Excel

by

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 …

Continue Reading

Sum by month ignore year in Excel

by

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 …

Continue Reading

How to calculate Quarter of Date in Excel

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Split numbers from units of measure in Excel
  • How to convert text string to array in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • Remove text by variable position in a cell in Excel
  • How to get page from URL in Excel
  • How to get year from date in Excel
  • Series of dates by day
  • Convert decimal hours to Excel time
  • Add days exclude certain days of week in Excel
  • How to calculate nth day of year in Excel
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate simple interest in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles