How to Convert a Date to a Quarter and Year in Excel (3 Methods)

Dataset Overview

We’ll use a dataset called the Product arrival Date of ABC Shop to demonstrate these methods. This dataset consists of two columns B and C called Product and Date. the dataset ranges from cells B4 to C10.

Dataset of excel convert date to quarter and year


Method 1 – Using ROUNDUP Function

Steps

  •  Select cell D5, the cell where you want to display the quarter and year.

Roundup function excel convert date to quarter and year

  • In the selected cell, enter the following formula:
="Q"&ROUNDUP(MONTH(C5)/3,0) & "-" & YEAR(C5)

This formula combines the quarter (Q) and year based on the date in cell C5.

Roundup function excel convert date to quarter and year

  • Use the Fill Handle to copy the formula from cell D5 to D10.

Roundup function excel convert date to quarter and year

  • You’ll see the results as shown in the picture.

Roundup function excel convert date to quarter and year

How Does the Formula Work?

  • MONTH(C5)/3,0) & “-” & YEAR(C5): The MONTH function calculates the month value from C5, divides it by 3, and appends the year from C5.
  • “Q”&ROUNDUP(MONTH(C5)/3,0) & “-” & YEAR(C5): Q” is then concatenated with the rounded-up result of the MONTH function divided by 3, followed by a hyphen and the year from C5.

Read More: Convert Date to Day of Week in Excel


Method 2 – Applying INT Function

Steps

  • Select Cell D5 where you want the quarter and year information.

Int function excel convert date to quarter and year

  • In Cell D5, enter the following formula:
="Q" &INT((MONTH(C5)+2)/3) & "-" & YEAR(C5)

Int function excel convert date to quarter and year

  • Press Enter to get the desired result.
  • Copy the formula from cell D5 to D10 to obtain the quarter and year values.

Int function excel convert date to quarter and year

  • The desired quarter and years can be seen below:

Int function excel convert date to quarter and year

How Does the Formula Work?

  • MONTH(C5)+2)/3 & “-” & YEAR(C5): The MONTH function retrieves a month value from C5, then divides the result by 3. Additionally, the YEAR function extracts the year value from 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


Method 3 – Full Quarter and Year Using INT Function

Steps

  • Select Cell D5, where you want the full quarter and year.

Int function excel convert date to quarter and year

  •  Enter this formula:
="Quarter " & INT((MONTH(C5)+2)/3) & "-" & YEAR(C5)

  • Press Enter to see the result in cell D5.
  • Copy the formula from cell D5 to D10 to get the complete quarter and year details.

  • The result can be seen below:

How Does the Formula Work?

  • MONTH(C5)+2)/3 & “-” & YEAR(C5): The MONTH function obtaining the month value from C5, then dividing it by 3, and appending the year value from 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

Steps

  • Select cell D5, where you want to display the month and year.

  •  In Cell D5, enter the following formula:
=TEXT(C5,"mmm/yyyy")

This formula extracts the month (in abbreviated format) and the year from the date in cell C5.

  • Press Enter to get the desired result.
  • Use the Fill Handle to copy the formula from cell D5 to D10.

  • You’ll see the results as shown in the picture.

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


Things to Remember

  • Both the ROUNDUP and INT functions return integer values in the formulas.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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