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

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



RichNYC wrote:

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

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.


In article ,
RichNYC wrote:

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

In article ,
Dave Peterson wrote:

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.

Thanks for the correction.

JE McGimpsey wrote:

In article ,
Dave Peterson wrote:

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

"JE McGimpsey" wrote:

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.


In article ,
RichNYC wrote:

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

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" wrote:

Thanks for the correction.

JE McGimpsey wrote:

In article ,
Dave Peterson wrote:

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

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)



kza40381 wrote:

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" wrote:

Thanks for the correction.

JE McGimpsey wrote:

In article ,
Dave Peterson wrote:

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


--

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)

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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

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.

"Dave Peterson" wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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