How do I convert a specific date to a fiscal quarter ?

[Guest]

In a sales spreadsheet, the sale date is represented as a specific day
date...i.e 7/12/2006. I want to convert that date into a representative
quarter...like Q1FY07.
I tried formating the date as a month-year( Jul-06 ) and then doing a
find/replace to replace all Jul-06 with Q1FY07...but the date in the field is
actually still 7/12/2006, just formatted as Jul-06....so Find/Replace finds 0
records....help !!

 

[Dave Peterson]

July 1, 2007 is quarter 1 of 2007???

I'd use another cell:

I find this formula easy to modify:
="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3)

It'll return:
FY2007-Q1

I find that I usually sort in chronological order and putting the year first
makes it easier to do that. (But you could swap it if you want.)

 

[JE McGimpsey]

Do you really want to replace the date of sale data?

If so, use a helper column. I'll assume your Fiscal Quarters align with
entire months (e.g., Jul-Sep, Oct-Dec, Jan-Mar, Apr-Jun):

B1: ="Q" & INT(MOD(MONTH(A1)+5,12)/3)+1 & "FY" &
TEXT(DATE(YEAR(A1),MONTH(A1)+6,1),"yy")

Copy down as far as required. Copy the helper column, select the date
column. Select Edit/Paste Special, selecting the Values radio button.


OTOH, if you want to keep your date data, you can use a Pivot Table to
summarize the data, and PT's allow you to group dates into quarters.

 

[JE McGimpsey]

July 1, 2007 is quarter 1 of 2007???

Actually, the OP implied that July 1, 2007 would be quarter 1 of FY2008.

July 1 is a very common start date for fiscal years.

 

[Dave Peterson]

Thanks for the correction.

Actually, the OP implied that July 1, 2007 would be quarter 1 of FY2008.

July 1 is a very common start date for fiscal years.

 

[Guest]

Do you really want to replace the date of sale data?

If so, use a helper column. I'll assume your Fiscal Quarters align with
entire months (e.g., Jul-Sep, Oct-Dec, Jan-Mar, Apr-Jun):

B1: ="Q" & INT(MOD(MONTH(A1)+5,12)/3)+1 & "FY" &
TEXT(DATE(YEAR(A1),MONTH(A1)+6,1),"yy")

Copy down as far as required. Copy the helper column, select the date
column. Select Edit/Paste Special, selecting the Values radio button.


OTOH, if you want to keep your date data, you can use a Pivot Table to
summarize the data, and PT's allow you to group dates into quarters.

 

[Guest]

Hi Dave...i apologize if this is a novice question, but can you provide a
formula which shows qtrs as Jan-March, Apr-June-July-Sept, October-December?
Thank you so much!

 

[Dave Peterson]

January-March is qtr 1
April-June is qtr 2
July-September is qtr 3
October-December is qtr 4

="FY"&YEAR(A1)&"-Q"&INT(1+MOD(MONTH(A1)-1,12)/3)

I like this formula in general--if the fiscal year starts on the first of month
number #:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

So if the fiscal year starts on July 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3)

 

[Rick Rothstein \(MVP - VB\)]

January-March is qtr 1

April-June is qtr 2
July-September is qtr 3
October-December is qtr 4

="FY"&YEAR(A1)&"-Q"&INT(1+MOD(MONTH(A1)-1,12)/3)

Maybe I'm missing something, but why do you need the MOD function in the
calculation for the quarter?

="FY"&YEAR(A1)&"-Q"&(1+INT((MONTH(A1)-1)/3))

Rick

 

[Dave Peterson]

You don't need it when the start of the fiscal year is January 1st.

But it is required when the start of that fiscal year is a different month. And
I just modified my favorite formula <g>.

 

[kza40381]

Just wanted to thank you both so much for providing your solutions below. I
can't tell you how grateful I am for your sharing of knowledge.