Converting dates to fiscal quarters

[Guest]

Is there a way to easily convert a date to a fiscal quarter
Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-199

Thanks in advance for your respons

 

[Peo Sjoblom]

One way with the date in A1

="Q"&INT((MONTH(A1)+2)/3)&" - "&TEXT(YEAR(A1),"@")

 

[Edward Stansfeld]

try

=IF(MONTH(A1)<=3,"Q1-"&YEAR(A1),IF(MONTH(A1)<=6,"Q2-"&YEAR(A1),IF(MONTH(A1)<
=9,"Q3-"&YEAR(A1),"Q4-"&YEAR(A1))))

Edward

 

[Dave Peterson]

I think you could drop the text() function:

="Q"&INT((MONTH(A1)+2)/3)&" - "&YEAR(A1)

(just to save the fingers!)

 

[Interested Party]

Actually, this is converting to the Calendar Quarter.

In my company Fiscal Year begins April 1st.

I tried to shift (subtracting 1 from the resultant quarter), but that ended
me up with a Q0 in some cases.

Also how do I take the year variance into consideration (Jan, Feb and Mar
are actually part of the previous Fiscal Year)?

Thanks,
Steve

 

[Peo Sjoblom]

Try this amendment

="Q"&INDEX({4;1;2;3},INT((MONTH(A1)+2)/3))&" -
"&IF(INT((MONTH(A1)+2)/3)=1,YEAR(A1)-1,YEAR(A1))

this assumes that a date like 03/30/04 should be Q4 - 2003



--

Regards,

Peo Sjoblom

Actually, this is converting to the Calendar Quarter.

In my company Fiscal Year begins April 1st.

I tried to shift (subtracting 1 from the resultant quarter), but that ended
me up with a Q0 in some cases.

Also how do I take the year variance into consideration (Jan, Feb and Mar
are actually part of the previous Fiscal Year)?

Thanks,
Steve

 

[Ron Rosenfeld]

In my company Fiscal Year begins April 1st.

Try this:

="Quarter "&CEILING(MONTH(DATE(1,MONTH(A1)-3,1))/3,1)&-YEAR(A1)




--ron

 

[Interested Party]

Thanks for that, Ron, but the Year portion isn't taking the shift into
consideration -- in addition to being the 4th Quarter, they remain in the
previous Fiscal Year.

So a date of, say, 1/14/04 (Jan 14th, 2004) would actually be Q4 - 2003 (not
2004).

 

[Peo Sjoblom]

Did you read my second post?

="Q"&INDEX({4;1;2;3},INT((MONTH(A1)+2)/3))&" -
"&IF(INT((MONTH(A1)+2)/3)=1,YEAR(A1)-1,YEAR(A1))

 

[Jay]

Is there a way to easily convert a date to a fiscal quarter?
Eg 1/1/03 converted to Q1-2003, 12/7/98 to Q4-1998

Places I've worked liked to have fiscal quarters not lined up with calendar
months. For some reason, they wanted fiscal quarters to end on Fridays.

For this situation, you might want to use a lookup table.

In Sheet 2, put something like these entries in the upper left 8 cells:
04/03/2004 Q1-2004
07/03/2004 Q2-2004
10/02/2004 Q3-2004
01/01/2005 Q4-2004

In sheet 1, if you have a column of dates in column A, then in B1 put
=IF(A2<DATE(2005,4,2), VLOOKUP(A2,Book1!$A$1:$B$4,2,TRUE), NA())
and extend down the column.

Modify to meet your needs.

 

[Ron Rosenfeld]

Thanks for that, Ron, but the Year portion isn't taking the shift into
consideration -- in addition to being the 4th Quarter, they remain in the
previous Fiscal Year.

So a date of, say, 1/14/04 (Jan 14th, 2004) would actually be Q4 - 2003 (not
2004).

Well that's easy enough to change:

="Quarter
"&CEILING(MONTH(DATE(1,MONTH(A1)-3,1))/3,1)&-YEAR(DATE(YEAR(A1),MONTH(A1)-3,1))


--ron