How do you convert date to fiscal quarter in Excel?

Answer 1

There is no Excel format that will display a fiscal quarter for a date. However, you can accomplish this with a complex formula, using IF, AND, and DATE functions.

1) In cell A1 put a date and ensure the cell is formatted for date. You can use any display format you like, as long as the cell is formatted as a date. For the purposes of this example, use the date 5/5/2010.

2) In cell A2 put the following formula:

=IF(AND(A1>=DATE(2010,1,1),A1<=DATE(2010,3,31)),"1st QTR",IF(AND(A1>=DATE(2010,4,1),A1<=DATE(2010,6,30)),"2nd QTR",IF(AND(A1>=DATE(2010,7,1),A1<=DATE(2010,9,30)),"3rd QTR",IF(AND(A1>=DATE(2010,10,1),A1<=DATE(2010,12,31)),"4th QTR"))))

3) Observe cell A2 displays 2nd QTR and will adjust based on the date you enter in cell A1.

4) Change the fiscal quarter names to whatever you like, and modify the formula for your specific application.

NOTE: This formula does not have any error handling, so you will receive a response of FALSE if your input date is not a valid date. You could add another IF statement at the end of the formula to display a more helpful error message.

----

ALTERNATIVE OPTION NOT LIMITED TO A SPECIFIC YEAR WITHIN FORMULA

DISPLAY THE CORRESPONDING QUARTER FOR A DATE WITH FORMULAS FOR COMMON FISCAL YEAR BEGINNING MONTHS:

(FORMULA PRODUCES: "Q#")

January

="Q"&FLOOR(((MONTH($A2)+2)/3),1)

April

="Q"&IF(FLOOR((((MONTH($A2)+2)/3)-1),1)>0,FLOOR((((MONTH($A2)+2)/3)-1),1),4)

July

="Q"&IF((((MONTH($A2)+2)/3)-2)<1,FLOOR((((MONTH($A2)+2)/3)-2)+4,1),FLOOR((((MONTH($A2)+2)/3)-2),1))

October

="Q"&IF(FLOOR((((MONTH($A2)+2)/3)-0),1)<4,FLOOR((((MONTH($A2)+2)/3)-0),1)+1,FLOOR((((MONTH($A2)+2)/3)-3),1))

FORMULA FOR DETAILED FORMAT (FISCAL YEAR STARTING JULY 1)

(FORMULA PRODUCES: "FY2012-13 Q3")

="FY"&IF(MONTH(A2)<7,YEAR($A2)-1,YEAR($A2))&"-"&RIGHT((IF(MONTH(A2)<7,YEAR($A2)-1,YEAR($A2))+1),2)&" Q"&IF((((MONTH($A2)+2)/3)-2)<1,FLOOR((((MONTH($A2)+2)/3)-2)+4,1),FLOOR((((MONTH($A2)+2)/3)-2),1))

[Above formulas refer to date in cell A2]