How to Convert Date to Quarter and Year in Excel

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.


Download Practice Workbook

Please download the workbook to practice yourself.


3 Suitable Methods to Convert Date to Quarter and Year in Excel

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 and Year. Let’s follow the procedures step by step. I have added some illustrations also for your better understanding.

Steps:

  • First, select the D5 cell.

Roundup function excel convert date to quarter and year

  • Then, 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

  • After that, 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

  • Consequently, you will find the result like 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): Finally, the ROUNDUP Function rounds the returned value of the MONTH function.

Read More: How to Convert Date to Julian Date in Excel (3 Easy Ways)


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:

  • In the very first step, select the D5 cell.

Int function excel convert date to quarter and year

  • After that, 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.
  • Then, copy down the formula from D5 to D10 cell.

Int function excel convert date to quarter and year

  • Consequently, 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): 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” & INT((MONTH(C5)+2)/3) & “-” & YEAR(C5): Finally, the INT Function rounds the returned value of the MONTH function.

Read More: How to Convert General Format to Date in Excel (7 Methods)


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 I will show the full quarter in this method. So, without further delay, let’s jump into the problem’s solution.

Steps:

  • Select the D5 cell first.

Int function excel convert date to quarter and year

  • Then, 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 then.

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

🔎 How Does the Formula Work?

  • MONTH(C5)+2)/3 & “-” & 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.
  • “Quarter ” & INT((MONTH(C5)+2)/3) & “-” & YEAR(C5): Finally, the INT Function rounds the returned value of the MONTH

Read More: Stop Excel from Converting Date to Number in Formula (2 Ways)


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 the D5 cell first.

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

  • Then press Enter and copy down the formula from D5 to D10 cells.

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

Read More: Convert Text to Date and Time in Excel (5 Methods)


Things to Remember

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

Conclusion

In this article, I have tried to explain Excel convert 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 Exceldemy.com. 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

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo