Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Quarter Dates in Excel

Excel is a combination of numeric values and alphabets. The user's project consists of the date depending upon their accounting and financial analysis. Sometimes there is a need to find the date and year present in the data belonging to which quarter. The formula method is used to find the quarter of the given date where the default function is not present in Excel.

How to find the quarter of the given date in Excel?

To find the quarter of the given date, the months are classified based on users' preferences as follows,

Quarter 1- Jan, Feb, Mar

Quarter 2-April, May, June

Quarter 3- July, Aug, Sep

Quarter 4- Oct, Nov, Dec

Step 1: Enter the required date in the column range A1:A10.

Quarter Dates in Excel

Step 2: Select a new cell, B1, and enter the formula as =ROUNDUP (MONTH (Cell name)/ 3, 0). Here A1 is mentioned as the cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

Quarter Dates in Excel

From the above formula, the month function retrieves the month from the given data and divides it with the number 3. The ROUNDUP function is used to round the value to the nearest integer. Finally, the value obtained displays the date present in which quarter. The above worksheet shows the result in column B1:B10, which consists of which quarter the date is present.

Example 1: Suppose the user displays the predefined quarter based on the project. How to calculate it?

Here in this example, the user displays the quarter as follows,

Quarter 1- April, May, and June

Quarter 2- July, Aug, Sep

Quarter 3- Oct, Nov, Dec

Quarter 4- Jan, Feb, Mar

The formula is modified as follows,

=IF(ROUNDUP(MONTH(A1)/3,0)-1=0,4,ROUNDUP(MONTH(A1)/3,0)-1).

Let’s implement this formula in the data as follows,

Step 1: Enter the required date in the column range A1:A10.

Quarter Dates in Excel

Step 2: Select a new cell namely B1 and enter the formula as =IF(ROUNDUP(MONTH(A1)/3,0)-1=0,4,ROUNDUP(MONTH(A1)/3,0)-1).Here A1 is mentioned as cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

Quarter Dates in Excel

The above worksheet shows the result in column B1:B10, which consists of which quarter the date is present. Here ROUNDUP function correctly displays the quarter value for all except the 4th quarter. It gives the result 0 for the 4th quarter. To rectify this, the IF function is implemented. If the quarter value is 0, the IF function returns the 4th quarter.

Example 2: How to display the word quarter in the cell?

Here, the word quarter is displayed in the cell using the formula. The steps to be followed are,

Step 1: Enter the required date in the column range A1:A10.

Quarter Dates in Excel

 Step 2: Select a new cell, namely B1, and enter the formula as =”Quarter”&ROUNDUP (MONTH (A1)/3, 0). Here A1 is mentioned as the cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

Quarter Dates in Excel

The above worksheet's result is displayed in column B1:B10, along with the word quarter. Either the word “q” or “Quarter” is added.

Example 3: How to extract the quarter of the date using CHOOSE function?

Excel provides another way to calculate the quarter of the given date. The CHOOSE function helps to find the quarter of the date. The steps to be followed are,

Step 1: Enter the required date in the column range A1:A10.

Quarter Dates in Excel

Step 2: Select a new cell, B1, and enter the formula as =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4). Here A1 is mentioned as the cell name. The formula works based on first getting the month of the given date, and the index in the formula helps display the required quarter.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

Quarter Dates in Excel

From the above worksheet, the result is displayed in the column range B1:B10 using the CHOOSE function.

Example 4: How to calculate the quarter of the date for the non-calendar year?

To calculate the quarter for the non-calendar years, the months are classified as follows,

Quarter 1- July, Aug, Sep 

Quarter 2- Oct, Nov, Dec

Quarter 3- Jan, Feb, Mar

Quarter 4- April, May, and June

The steps to be followed to calculate the quarter of the date are as follows,

Step 1:  Enter the required date in the column range A1:A10.

Quarter Dates in Excel

 Step 2: Select a new cell, B1, and enter the formula as =CHOOSE(MONTH(A1),3,3,3,4,4,4,1,1,1,2,2,2). Here A1 is mentioned as the cell name.

Step 3: Press Enter. Drag the formula toward the required cell range to display the result for the remaining cells. The result will be displayed in cell B1.

Quarter Dates in Excel

From the above worksheet, the result is displayed in the column range B1:B10 using the CHOOSE function where the order of index is changed based on the data.

Summary

Multiple projects contain various dates. Hence it is necessary to arrange the data based on the date. The formula will vary based on the quarter of the date. The above tutorial explains the various functions and methods used to calculate the quarter of the date.