How to Convert Date to Quarter and Year in Excel

Get FREE Advanced Excel Exercises with Solutions!

While working on an Excel worksheet, you need to handle different kinds of procedures. One of them is to convert date to quarter and year in Excel. You can convert date to quarter and year in Excel by using some easy methods. Here in this article, I will show you Excel converting date to quarter and year. I will show you three suitable methods here. I hope you will get more skills to develop yourself in operating Excel.


How to Convert Date to Quarter and Year in Excel: 3 Methods

In this article, I will consider a dataset called the Product arrival Date of ABC Shop. This dataset consists of two columns B and C called Product and Date. the dataset ranges from B4 to C10 cells. I will use this dataset and show you the procedures of Excel converting date to quarter and year. I hope you will get interested in the methods.

Dataset of excel convert date to quarter and year


1. Using ROUNDUP Function to Convert Date to Quarter and Year in Excel

This is the first method of this article. I will show here Excel convert date to quarter and year. I will use the ROUNDUP function here. Moreover, I have added a new column in the dataset called Quarter & Year. Let’s follow the procedures step by step. I have added some illustrations also for your better understanding.

Steps:

  • Select the D5 cell.

Roundup function excel convert date to quarter and year

  • Write down the following formula in the selected cell.
="Q"&ROUNDUP(MONTH(C5)/3,0) & "-" & YEAR(C5)

Roundup function excel convert date to quarter and year

  • You will find the following result shown in the picture.
  • Then, Fill-handle to copy down the formula from cell D5 to D10.

Roundup function excel convert date to quarter and year

  • You will find the result in the picture given below.

Roundup function excel convert date to quarter and year

🔎 How Does the Formula Work?

  • MONTH(C5)/3,0) & “-” & YEAR(C5): Here, the MONTH function returned a value according to a month and got divided by 3 and the YEAR function returns the year value of C5.
  • “Q”&ROUNDUP(MONTH(C5)/3,0) & “-” & YEAR(C5): The ROUNDUP function rounds the returned value of the MONTH function.

Read More: Convert Date to Day of Week in Excel


2. Converting Date to Quarter and Year in Excel by Applying INT Function

This is the second method of this article. I will use the INT function to convert date to quarter in Excel. Let’s follow the steps of the procedure. You can also take help from the images I have added with the steps.

Steps:

  • Select cell D5.

Int function excel convert date to quarter and year

  • Write down the following formula in the selected cell.
="Q" &INT((MONTH(C5)+2)/3) & "-" & YEAR(C5)

Int function excel convert date to quarter and year

  • After pressing Enter, you will get the desired result.
  • Copy down the formula from cell D5 to D10.

Int function excel convert date to quarter and year

  • You will get the desired quarter and years like the following picture.

Int function excel convert date to quarter and year

🔎 How Does the Formula Work?

  • MONTH(C5)+2)/3 & “-” & YEAR(C5): The MONTH function returned a value according to a month and got divided by 3. And the YEAR function returns the year value of C5.
  • “Q” & INT((MONTH(C5)+2)/3) & “-” & YEAR(C5): The INT function rounds the returned value of the MONTH function.

Read More: How to Convert Date to Year in Excel


3. Inserting INT Function to Convert Date to Full Quarter and Year in Excel

This is the last but not the least method to Excel convert date to quarter and year. I will use the INT function again. But, the difference between the second and third methods is that we will get the full quarter in this method. Without further delay, let’s jump into the problem’s solution.

Steps:

  • Select cell D5 first.

Int function excel convert date to quarter and year

  • Copy the following formula in the selected cell.
="Quarter " & INT((MONTH(C5)+2)/3) & "-" & YEAR(C5)

  • After pressing the Enter button, you will get the desired result in cell D5.
  • Copy down the formula from cell D5 to D10.

  • At last, you will find the result just like the picture given below.

🔎 How Does the Formula Work?

  • MONTH(C5)+2)/3 & “-” & YEAR(C5): The MONTH function returned a value according to a month and got divided by 3. And the YEAR function returns the year value of C5.
  • “Quarter ” & INT((MONTH(C5)+2)/3) & “-” & YEAR(C5): The INT function rounds the returned value of the MONTH function.

How to Convert Date to Month in Excel

Besides Excel converting date to quarter and year, you may need to convert date to month in Excel also. In this portion of the article, I will show you how to convert date to month in Excel. I consider the same dataset which I have considered in the earlier three methods. I have added the necessary steps as well as illustrations.

Steps:

  • Select cell D5 first.

  • Then, copy the following formula in the selected cell.
=TEXT(C5,"mmm/yyyy")

  • Press Enter and copy down the formula from cell D5 to D10.

  • You will find the result just like the picture given below.

Read More: How to Convert Date to Month and Year in Excel


Things to Remember

  • ROUNDUP and INT both functions return an integer value in the formulas.

Download Practice Workbook

Please download the workbook to practice yourself.


Conclusion

In this article, I have tried to explain how Excel converts date to quarter and year. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website. I hope you have enjoyed the whole tutorial. If you have any kind of queries feel free to ask me in the comment section. Don’t forget to give us your feedback.


Related Articles


<< Go Back to Change Date Format | Date Format | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

2 Comments
  1. by using this formula =IF(MONTH(I2)>3,”Q”&ROUNDUP(MONTH(I2)/3,0)-1&” FY-“&RIGHT(YEAR(I2),2),”Q4 FY-“&RIGHT(YEAR(I2),2)) result will be= Q4 FY-19

    by using this formula =IF(MONTH(I2)>3,”Q”&ROUNDUP(MONTH(I2)/3,0)-1&” FY-“&RIGHT(YEAR(I2),2),”Q4 FY-“&YEAR(I2)) result will be= Q4 FY-2019

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo