• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert dates to Fiscal Quarters

ScottLewin

ScottLewin

New Member
I frequently have to prepare analyses of transaction data which are organized by dates. The best way to present the data is to group by fiscal quarters. Unfortunately our fiscal year is different from the calendar year, so I can't simply convert to a pivot table and use timelines to present the data, since timelines default to calendar quarters.
I've been sorting on date, then adding in the fiscal quarter label (as text) manually in an adjacent column, but I'm sure there has to be a much better way to go about this. I don't know how to code using VBA, so if anyone knows how to accomplish with formulas, or other functions, it would be greatly appreciated!!
Cheers,

Scott
 
Hi Scott, and welcome to the forum! :awesome:

I'm going to take a guess that the easiest solution is to add a helper column with some formula to converts your dates into fiscal year. Whether we do this manually or via macro we can decide later.

However, we do need to know how to translate the dates. What are the cut-off points for your fiscal quarters?
E.g.,
Mar 1 = Start of Q1
Jul 1 = Start of Q2
Aug 1 = Start of Q3
Dec 1 = Start of Q4
 
Awesome Luke - thanks for the help!

Fiscal quarters break down like this:

Apr 1 - start of Q1
Jul 1 - start of Q2
Oct 1 - start of Q3
Jan 1 - start of Q4
 
Source: http://www.excelvlookuphelp.com/
If you want a shorter formula, you can use a vlookup with an accompanying table...


This is possible using a VLOOKUP. Remember the structure is:
=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )
Example Quarterly Data VLOOKUP Scenario
In the example, we would like to populate column C in the Data tab so that there is a quarter associated with each Sales Date (in column A).



By including the Financial Year in the data, it will enable us to include an automatic summary table (see Summary tab in the Tutorial Workbook) and will allow us to fill in the Sales Total column below

In order to do this, we will need to create a lookup table which in the example file is done on the Lookup tab. Importantly, the lookup values in column A are the start months for each quarter.





Solution VLOOKUP formula
So using the standard structure of

=VLOOKUP( SearchFor , WhereToSearch , WhichColumn ,NearOrExact )

where SearchFor is based on the Sales Date.
This will be based on the month part of the date, so our SearchFor term will use the MONTH excel formula -> MONTH(A2)
WhereToSearch is the list of Dates and Financial Years -> Lookup Tab A:B
Which column is the second column -> 2
NearOrExact is Near -> True
Our formula looks like =VLOOKUP(MONTH(A2),Lookup!A:B,2,TRUE)







Which produces the result in our data as follows




… and enables us to compete the summed invoiced amounts by Quarter on the Summary tab using a SUMIF formula

Want to get super fancy? You can change this to financial/fiscal year quarters if you combine it with our tutorial here

Source: http://www.excelvlookuphelp.com/tutorial-how-to-use-vlookup-to-add-quarters-to-your-date-information-u-k-format/
 
Luke M said:
Assuming your data is in A2, formula to convert to fiscal quarter then is:
="Q"&CHOOSE(INT((MONTH(A2)+8)/3)-2,4,1,2,3)&" "&YEAR(A2)-(MONTH(A2)<4)
Click to expand...

Dear All

just added some years mentioning formula ,
This mentioning fiscal "years" in result. if quarter start from April ..

="Q" & CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3) & IF( MONTH(A2)<=3 &" " & "FY" & " " & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2), "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )

hope this will be helpful...
Regards,

Chirag Raval
 
Dear All,

This is Final...
perfect combination of mentioning in result "Quarter & Between Years )

="Q"&CHOOSE(INT((MONTH(A2)+8)/3)-2,4,1,2,3)&" "& IF(MONTH(A2)<=3,"FY"&(YEAR(A2)-1)&"/"&RIGHT(YEAR(A2),2),"FY"&YEAR(A2)&"/"&RIGHT((YEAR(A2)+1),2))

Regards,
Chirag Raval
 
You're formula is missing a comma separating the test condition from the True argument in the IF function.

="Q" & CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3) & IF( MONTH(A2)<=3,"FY" & " " & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2), "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )

Fix that, and your other dates will be correct.
 
Luke M said:
You're formula is missing a comma separating the test condition from the True argument in the IF function.

="Q" & CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3) & IF( MONTH(A2)<=3,"FY" & " " & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2), "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )

Fix that, and your other dates will be correct.
Click to expand...

Dear Sir,

That's Great, Thank you very much for kind help..

regards,
Chirag Raval
 
Dear Sir @bossco_yjp,

Thank you very much for contribute your new aspect about
Fiscal period of year, month..

I will also practice on your amazing new formula & revert back.

Regards,

Chirag Raval
 
Dear all Masters,

Can be do it in reverse order ?

Like " Fy 2017/18 Q1" ? Because
There need some time to create pivot table,
When 3 or more fiscal years & month quarters
For compare , require years in sorted order ,
past years to current. With also it's fiscal quarters
Also sorted orders from 1 to 4 either vertically or horizontally.
In short first priority is years then quarters.

If it will be success, then also problem can be raise due to use "Fy" as prefix "Fy"
In sort Order in pivot table. we can either avoid it or how can use another
Approach?

Remember that this helper column have number & text combined.
So it's format as either text or general. & key factor for sorting is first year and then it's quarters
So how to construct this helper column's formula that it properly sort in pivot table?
put it in pivoted table either horizontal order or vertical?

Hope there some solutions there.

Regards,

Chirag Raval
 
Last edited:
Dear Sir @GraH - Guido ,

Just Amazing, Work perfect as desired,

Thank you very much for your kind contribution.
it also naturally sorted in pivot table as date or numbers.

Though , please share any tips, if in you mind , for sort without fail in pivot table.

Regards,
Chirag Raval
 
You are welcome Chirag.
Sorting tips depend a bit on the sorting need. It can be done on dimensions and aggregates (totals, rank, ...), on rows and columns.

I believe you have managed to describe the tip for sorting on your own for your specific need. The "string" must be made as such so it can be sorted in a logical alphanumerical matter.
Like for month references I always use a YEAR - MONTH (2018-01) reference, with month always in 2 positions.
And an often forgotten feature is the sort by Custom List (e.g. names of weekdays, or names of month are defaults). One can make his own lists and thus sorting criteria.
 
You must log in or register to reply here.
Back
Top