Grouping dates into Fiscal Yrs/Qtrs/Months

  • Thread starter Thread starter James
  • Start date Start date
J

James

I just learned how to group dates into years/quarters/months. How do I change
this to reflect a fiscal cycle (July - June)?
 
James,

You could use helper columns - with dates in column A, starting in A2:

For the fiscal year:
=YEAR(A2)-(MONTH(A2)<7)

For the quarter:
=INT(1+MOD(MONTH(A2)-7,12)/3)

For it all combined into one
="FY"&YEAR(A2)-(MONTH(A2)<7)&"-Q"&INT(1+MOD(MONTH(A2)-7,12)/3)

Then copy the formulas down to match your dates.

HTH,
Bernie
MS Excel MVP
 
James,

You could use helper columns - with dates in column A, starting in A2:

For the fiscal year:
=YEAR(A2)-(MONTH(A2)<7)

For the quarter:
=INT(1+MOD(MONTH(A2)-7,12)/3)

For it all combined into one
="FY"&YEAR(A2)-(MONTH(A2)<7)&"-Q"&INT(1+MOD(MONTH(A2)-7,12)/3)

Then copy the formulas down to match your dates.

HTH,
Bernie
MS Excel MVP
 
Back
Top