Dates to quarters

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 our 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 this with a seperate formula or adjust the one Im using ? If so in either case can someone show me how as Im usless with date formulas.

Thanks

Something like this?

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

Hans

Something like this?

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

Hans

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



"Ainsley" <[email protected]> wrote in
message news:[email protected]...
>
> 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 our
> 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 this
> with a seperate formula or adjust the one Im using ? If so in either
> case can someone show me how as Im usless with date formulas.
>
> Thanks
>
>
> --
> Ainsley
> ------------------------------------------------------------------------
> Ainsley's Profile:
> http://www.excelforum.com/member.php...o&userid=31960
> View this thread: http://www.excelforum.com/showthread...hreadid=516820
>

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

"Ainsley" <[email protected]> wrote in
message news:[email protected]...
>
> 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 our
> 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 this
> with a seperate formula or adjust the one Im using ? If so in either
> case can someone show me how as Im usless with date formulas.
>
> Thanks
>
>
> --
> Ainsley
> ------------------------------------------------------------------------
> Ainsley's Profile:
http://www.excelforum.com/member.php...o&userid=31960
> View this thread: http://www.excelforum.com/showthread...hreadid=516820
>

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)

"R..VENKATARAMAN" <[email protected]> wrote in message
news:[email protected]...
> 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))
>
>
>
> "Ainsley" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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 our
> > 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 this
> > with a seperate formula or adjust the one Im using ? If so in either
> > case can someone show me how as Im usless with date formulas.
> >
> > Thanks
> >
> >
> > --
> > Ainsley
> > ------------------------------------------------------------------------
> > Ainsley's Profile:
> > http://www.excelforum.com/member.php...o&userid=31960
> > View this thread:
http://www.excelforum.com/showthread...hreadid=516820
> >
>
>

Thanks for that, it works well.