Share this content
0
1668

Excel - date formula for fiscal quarter

Excel 2010 - Formula for fiscal quarter

Didn't find your answer?

Search AccountingWEB

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.

26th Jan 2017 15:40

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","")

Thanks (1)
to scalloway
26th Jan 2017 16:13

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.

Thanks (0)
26th Jan 2017 15:47

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

Thanks (1)
to alan.rolfe
26th Jan 2017 16:11

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

Thanks (0)
to Dick Lloyd
26th Jan 2017 18:16

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?

Thanks (0)
avatar
to Dick Lloyd
26th Jan 2017 18:34

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

Thanks (1)
to paulwakefield1
26th Jan 2017 19:11

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.

Thanks (0)
avatar
to Dick Lloyd
26th Jan 2017 22:24

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.

Thanks (1)
to paulwakefield1
27th Jan 2017 13:33

Many thanks, Paul.

This now works perfectly.

Kind regards,

Dick

Thanks (0)
to paulwakefield1
26th Jan 2017 19:13

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.

Thanks (0)
avatar
26th Jan 2017 16:24

A slightly shorter formula for you:

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

Thanks (2)
to paulwakefield1
26th Jan 2017 18:14

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.

Thanks (0)
avatar
to Dick Lloyd
26th Jan 2017 18:36

Good point, well made. :-)

Thanks (0)
avatar
to Dick Lloyd
27th Jan 2017 08:19

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!)

Thanks (0)
avatar
10th Feb 2017 16:49

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

Thanks (0)
avatar
16th Feb 2017 11:47

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?

Thanks (0)
avatar
to rjmannaca
17th Feb 2017 09:09

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.

Thanks (0)
Share this content