Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Calculate Financial Quarter from a date

Posted on 2011-10-19
Medium Priority
751 Views
I want to know if there is a simpler way to calculate the Financial Quarter

Sorry to add another question on this topic but I failed to find a simple answer which would work for any date (happy to be redirected).  I don't want to write out a special table manually but to work it out mathematically.
The quarter "1" is Jul - Sep, "2" is Oct - Dec, etc

The following returns what I want, but idealy I would not use the IF() statement, and only reference the date cell once if possible.
=ROUNDUP(MONTH(A2)/3,0)+IF(MONTH(A2)>6,-2,2)

The simplest/shortest solution using vba or excel formula.

Thanks
0
Question by:_Benaiah
• 3
• 2

LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 36997061
Hello _Benaiah

Try using LOOKUP, it can be adjusted depending on where your quarters start

=LOOKUP(MONTH(A2),{1,4,7,10},{3,4,1,2})

...or this will give the same result

=MOD(INT((MONTH(A2)+5)/3),4)+1

regards, barry
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 36997081
=MOD(ROUNDUP(MONTH(A3)/3,0)+1,4)+1
0

LVL 43

Expert Comment

ID: 36997088
Hi Barry, I did not expect you at this time.
0

LVL 1

Author Closing Comment

ID: 36997425
Barry's formula slightly more suited to my purposes but thanks both of you
0

LVL 50

Expert Comment

ID: 36998892
>I did not expect you at this time
Hehe....nor did I!

One more for fun?

=MID(3412,MONTH(A2)/3+2/3,1)+0

regards, barry
0

LVL 43

Expert Comment

ID: 36999905
Beautiful
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month15 days, 3 hours left to enroll