Souptik Roy
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.
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.
- Then, write down the following formula in the selected cell.
="Q"&ROUNDUP(MONTH(C5)/3,0) & "-" & YEAR(C5)
- After that, you will find the following result shown in the picture.
- Then, Fill-handle to copy down the formula from D5 to D10
- Consequently, you will find the result like the picture given below.
🔎 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.
- After that, write down the following formula in the selected cell.
="Q" &INT((MONTH(C5)+2)/3) & "-" & YEAR(C5)
- After pressing Enter, you will get the desired result.
- Then, copy down the formula from D5 to D10 cell.
- Consequently, you will get the desired quarter and years like the following picture.
🔎 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
- How to Convert Unix Timestamp to Date in Excel (3 Methods)
- How to Convert Active Directory Timestamp to Date in Excel (4 Methods)
- How to Disable Auto Convert to Date in Excel (2 Methods)
- How to Convert SAP Timestamp to Date in Excel (4 Ways)
- How to Convert Week Number to Date in Excel (2 Suitable Methods)
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.
- 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
- How to Convert Text to Date in Excel (10 ways)
- How to Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)
- Text Won’t Convert to Date in Excel (4 Problems & Solutions)
- How to Convert Number to Date in Excel (6 Easy Ways)
- How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
- Excel VBA to Convert Date and Time to Date Only
