vsbabu.org

FYs in Excel

I use Microsoft Excel for a variety of needs. One thing I need to do often in that is analyse data by Fiscal Year and Quarters.

Here are two formulas I use in almost every sheet where I've a date column.

If cell A2 has a date, this formula will return the corresponding FY as a two digit number.

=RIGHT(INT(IF(MONTH(A2)>=7,YEAR(A2)+1,YEAR(A2))),2)

If cell A2 has a date, this formula will return the corresponding quarter, as a one digit number.

=INT(IF(MONTH(A2)>=7,(MONTH(A2)-1)/3-1,(MONTH(A2)-1)/3+3))

And if you've the first formula in B2 and second formula in C2, you can concatenate these to display the information like FY04Q1.

="FY"&B2&"Q"&C2