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 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: How to Use Formula to Change Date Format 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 D5 cell.

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 D5 to D10 cell.

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 Julian Date in Excel


Similar Readings


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 D5 cell 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 the D5 cell.
  • Copy down the formula from D5 to D10 cell.

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

Read More: Stop Excel from Converting Date to Number in Formula


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. Let’s follow the steps one by one.

Steps:

  • Select D5 cell first.

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

  • Press Enter and copy down the formula from 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

Souptik Roy
Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo