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
6
Medium Priority
?
751 Views
Last Modified: 2012-05-12
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
Comment
Question by:_Benaiah
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
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

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

Expert Comment

by:Saqib Husain, Syed
ID: 36997088
Hi Barry, I did not expect you at this time.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Closing Comment

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

Expert Comment

by:barry houdini
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

by:Saqib Husain, Syed
ID: 36999905
Beautiful
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question