Fiscal Quarter format

  • Thread starter Thread starter EggCell
  • Start date Start date
E

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

Bob Phillips said:
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)
Click to expand...
 
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)

Bob Phillips said:
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)

Bob Phillips said:
Try this
Click to expand...
="FY"&TEXT(DATE(YEAR(D4),MONTH(D4)-5,1),"yyQ")&INT((MONTH(DATE(YEAR(D4),MONT
Click to expand...
 
Can't seem to force it onto one line, just patch it together when using.

--
Click to expand...

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
 
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)
 
Back
Top