Dates to quarters

[Ainsley]

Hi, Ive found on this forum a formula which turns a date (dd,mm,yy
into a quarter,year which is great:

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

However its set up to take Jan Feb and Mar as quarter 1, but ou
financial year starts in April. How do i adjust it to represent this.
The formula looks at a date (dd,mm,yy) in column C, should I change thi
with a seperate formula or adjust the one Im using ? If so in eithe
case can someone show me how as Im usless with date formulas.

Thank

 

[hansyt]

Something like this?

="Q"&INT((MONTH(C1)-1)/3)+(MONTH(C1)<4)*4 & "
"&(YEAR(C1)-(MONTH(C1)<4)*1)

Hans

 

[hansyt]

Something like this?

="Q"&INT((MONTH(C1)-1)/3)+(MONTH(C1)<4)*4 & "
"&(YEAR(C1)-(MONTH(C1)<4)*1)

Hans

 

[R..VENKATARAMAN]

try this
=IF(MONTH(C2)>3,"Q"&INT((MONTH(C2)-4)/3)+1 & " " &
YEAR(C2),"Q"&INT((MONTH(C2)-4+12)/3)+1 & " " & YEAR(C2))

 

[Bob Phillips]

="Q"&INT((MONTH(DATE(YEAR(C2),MONTH(C2)-3,1))-1)/3)+1 & " " & YEAR(C2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Correction

="Q"&INT((MONTH(DATE(YEAR(C2),MONTH(C2)-3,1))-1)/3)+1&"
"&YEAR(DATE(YEAR(C2),MONTH(C2)-3,1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ainsley]

Thanks for that, it works well