Fiscal Quarter format

[EggCell]

Cell D4 contains a date (20/11/2003), formatted to dd-mmm-y
(20-Nov-03). I want to show its corresponding fiscal year and quarte
(FY03Q2). Note again that this is not based on the calendar year, bu
our fiscal year where Q1 is Jun, Jul, Aug, Q2 is Sep, Oct, Nov, and s
on. What formula in cell R4 can do this

 

[Bob Phillips]

Try this

="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT
H(D4)-5,1))-1)/3+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

That shouldn't be broken

="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT
H(D4)-5,1))-1)/3+1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try this

="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT
H(D4)-5,1))-1)/3+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Can't seem to force it onto one line, just patch it together when using.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

That shouldn't be broken

="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT
H(D4)-5,1))-1)/3+1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try this

="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT

 

[Ron Rosenfeld]

Can't seem to force it onto one line, just patch it together when using.

--

In my newsreader "Agent", I find that if, in Excel, I break the lines in the
formula bar using <alt><enter> that when I Copy/Paste it into Agent, although
it will appear on several lines, copying it back into the formula bar results
in a valid formula.


--ron

 

[Bob Phillips]

Thanks Ron, I will give that a try in future, it sure looks a mess as is.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)