Does anyone have a formula that I can use in Excel 2010 that will return the fiscal quarter for a given date?

For example, I would want to return "2016/17 Q3" for a date of 01/12/2016 (and for any date between 01/10/2016 and 31/12/2016).

I'm sure there will be a solution, but it's not one I have ever seen.

Many thanks in advance.

## Replies

## Please login or register to join the discussion.

If you want to do it on a quarter by quarter basis put the start date in A1, end date in A2 and the values are in column B this will show Q3 results. Amend for each quarter.

=IF(AND(B1>$A$1,B1<$A$2),TEXT(B1,"dd/mm/yy")&" Q3","")

Many thanks, Scalloway.

As I want to create a daily model from 01/12/2016 to 31/03/2021, I would prefer not to do it on a quarter by quarter basis, but rather use a generic formula that would work for any given date in any fiscal quarter.

Still, your suggestion is much appreciated.

=IF(MONTH(TargetCell)>3,YEAR(TargetCell)&"/"&RIGHT(YEAR(TargetCell)+1,2) & " Q"&INT(MONTH(TargetCell)/4),YEAR(TargetCell)-1&"/"&RIGHT(YEAR(TargetCell),2)&" Q4")

Many thanks, Alan Rolfe. This is precisely what I wanted.

Alan, I have a slight problem with your formula. For dates in July 2016 it is returning "2016/17 Q1" instead of "2016/17 Q2". Any suggestions?

Try using /3 instead of /4 in the INT part of the formula.

If I use /3 instead of /4 as you suggest, it works for dates in July 2016 but now it shows "2016/17 Q2" instead of "2016/17 Q1" for dates in June 2016. Many thanks for trying.

Yes -I was thinking June was Q2. Doh!

So, substitute for "INT(MONTH(TargetCell)/4)" the following

ROUNDUP(MONTH(Targetcell)/3,0)-1

i.e. =IF(MONTH(Targetcell)>3,YEAR(Targetcell)&"/"&RIGHT(YEAR(Targetcell)+1,2) & " Q"&ROUNDUP(MONTH(Targetcell)/3,0)-1,YEAR(Targetcell)-1&"/"&RIGHT(YEAR(Targetcell),2)&" Q4")

This works as you expected.

Many thanks, Paul.

This now works perfectly.

Kind regards,

Dick

Thank you Paul.

However, if I try using /3 rather than /4 in the INT part of the formula, it works for dates in July 2016, but now shows "2016/17 Q2" rather than "2016/17 Q1" for dates in June 2016.

Many thanks for trying.

A slightly shorter formula for you:

="2016/17 Q"&CHOOSE(ROUNDUP(MONTH(target cell)/3,0),4,1,2,3)

Thank you Paul Wakefield. I think your suggestion would only work for the year 2016/17. The solution provided by Alan Rolfe above, although a longer formula, works for any date in any year, which is really what I was looking for.

All the same, much appreciated.

Good point, well made. :-)

That made me have a bit of a play to see if I could get a shorter formula with a variable year. Alan's is a better solution but FWIW, here is an alternative:

=TEXT(YEAR(Targetcell)*100+MOD(YEAR(Targetcell)+1,100)-(MONTH(Targetcell)<4)*101,"###\/##")&" Q"&CHOOSE(ROUNDUP(MONTH(Targetcell)/3,0),4,1,2,3)

A saving of 25 characters (though a bit less once targetcell is replaced with cell references).

Edit: Amended formula for consistency with other reply (and accuracy!)

Nice answers here. I have copied the two working answers into my digital Excel toolbox! Thank you both.

Coming to this a bit late, but I tried and revised the formula myself before reading to the bottom of the comments.

I simply revised the INT function to

INT((MONTH(TargetCell)-1)/3)

instead of replacing it with

ROUNDUP(MONTH(Targetcell)/3,0)-1

Are there any reasons why either would be preferable to the other?

I think that is a good solution. In a more general context, INT may evaluate to zero where Roundup will not but it would be rare that this would cause a problem.

Edit: To clarify, the above comment referred to when applying INT or ROUNDUP to a divided month number.

## Please login or register to join the discussion.