How do I convert a specific date to a fiscal quarter ?
"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 !!
|