How to get quarter of year from date

In this article, we will learn to generate random text values in  Excel.

For this article we will be needing the use the following functions:

  1. CHOOSE function
  2. MONTH function

The following are the description & syntax for the functions used in the formula:

CHOOSE function takes values and return the value corresponding to a particular index_num.
Syntax:

= CHOOSE (index_num, value1, [value2], …)

index_num: value to look in value array.
value1: first value of the value array.
value2: second value of the value array. And so on..

The MONTH function takes a valid excel date as an argument and returns the month number (1-12) of the date. 
Syntax:

= MONTH ( serial_number )

Serial_number : serial_number of date or valid excel date
Note: Date as a serial number must be a valid.

As we know the MONTH function returns a number between the 1 to 12 numbers ( months of the calender year ). And CHOOSE function will take that number and returns the value corresponding to the number.
Use the formula:

= CHOOSE ( MONTH ( date ) , "value1" , "value2" , "value3" )

Explanation: 

  1. The MONTH function returns a number from 1 - 12 (calender month of the year) of the valid input date.
  2. The CHOOSE function returns a corresponding value from the value array based on the number returned by the MONTH function.
  3. The value array is set as according to the required output. As quarter of year can starts from Jan, Apr, July or Oct. 

Let's test this formula via running it on an example

Here we have list of dates in Values column A2 : A10.


Here we have given the quarter year sub section in the snapshot above. We need to find the quarter period of the date in Values using the formula shown below.

Use the formula:

= CHOOSE ( MONTH (A2) , 1 , 1 , 1 , 2 , 2 , 2 , 3 , 3 , 3 , 4 , 4 , 4 )

Explanation:

  1. The MONTH function returns a number from 1 - 12 (calender month of the year) of the valid input date.
  2. The CHOOSE function returns a corresponding value from the value array {  1 , 1 , 1 , 2 , 2 , 2 , 3 , 3 , 3 , 4 , 4 , 4 } based on the number returned by the MONTH function.
  3. Here The date is from January. The Month function returns 1. And the value corresponding to 1 is 1.

As you can see the function returns 1 means first quarter of the year.

Here the date is provided as cell reference to the function. Now copy the formula to other cells using the Ctrl + D shortcut key or Use the drag down option in Excel.

As you can see in the above snapshot the outcomes of the formula.

You can get the outcome with text like Q3 (year). Use the formula shown below.

Use the formula:

= "Q" & CHOOSE ( MONTH (A2) , 1 , 1 , 1 , 2 , 2 , 2 , 3 , 3 , 3 , 4 , 4 , 4 ) & " (" & YEAR(A2) & ")"


As you can see from the above snapshot the customized format for the outcomes.
Below are some of the observational outcomes of the formula.
Notes :

  1. The CHOOSE function returns an error, if the MONTH function returns anything else except number from 1 - 12.
  2. The MONTH function returns error, if the argument date provided is invalid date as per Excel.
  3. The CHOOSE function returns #VALUE! Error if the value array argument to the function is given as array.

Hope this article about how to generate random text values in Excel is explanatory. Find more articles on TEXT functions here. Please share your query below in the comment box. We will assist you.

Related Articles

How to use the CHOOSE Function in Excel

How to use the MONTH Function in Excel

Find the nth root of a number

Calculate Weighted Average

Ignore zero in the Average of numbers

 

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Convert Inches To Feet and Inches in Excel 2016

Join first and last name in excel

Count cells which match either A or B

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube