Calculate a quarter

[JPS]

I have a large spreadsheet that is a five-year forecast. In one column is a
date, I need to know a way to calculate the quarter, in XQYY format, the
event is forecasted to happen. Where X is the quarter from the table below, Q
is a set value and XX is the forecast year.

Date Range Quarter
January – March 1
April - June 2
July – September 3
October – December 4

For example, March 2009, would be 1Q09.

Thanks,

 

[Fred Smith]

=TEXT(MONTH(A1)/4+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00")

Regards,
Fred.

 

[Gary''s Student]

With a date in A1:

=ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)

 

[JPS]

GSNU200786 & Fred

Both solutions worked
As always thanks for the help....
Thanks

 

[T. Valko]

Get incorrect results:

2/1/2008 = 2Q08
6/1/2008 = 3Q08

 

[T. Valko]

Here's another one:

=CEILING(MONTH(A1)/3,1)&"Q"&TEXT(A1,"yy")

 

[Fred Smith]

You're right. The fractions need to be ignored:

=TEXT(INT(MONTH(A1)/4)+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00")

Regards,
Fred/

 

[Dana DeLouis]

Too bad the worksheet doesn't have the same quarter formatting as vba.

Function Quarter_Year(d As Date)
Quarter_Year = Format(d, "q\Qyy")
End Function

--
Dana DeLouis

 

[Compben]

if cell A1 is empty I want it to report a 0 how can i do this. thanks in
advance. I am ccreating a template that calculate quarters but find that the
template without dates is hshowing that it is the 1st quarter.

 

[Roger Govier]

Hi

If you do want to show a 0 then use
=IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2))
or if you want the cell to remain blank, then
=IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2))

The reason it is returning Q1, is that empty date cells are taken to be
00/01/1900, hence they fall into Month 1

 

[compben]

excellent, thank you I have been working on this for days

 

[compben]

this formula worked
=IF(A1="",0,ROUNDUP(MONTH(A1)/3,0)

what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4?

I had modified the formula to this:
VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE)

Quarter New Quarter
1 1st
2 2nd
3 3rd
4 4th
but now I still have my original problem now the formula is reporting #NA
when the date cell is blank.

 

[T. Valko]

what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4?

Try this

=IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0)

 

[Roger Govier]

Hi

It should be
=IF(F262="","",VLOOKUP(INT((MONTH(F262)-1)/3)+1,Lookup!$A$1:$B$4,2,FALSE))

 

[compben]

excellent!!! thanks, this one worked perfectly.

Try this

=IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0)

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

excellent!!! thanks, this one worked perfectly.

 

[MMG]

In this formula, is there a way to change it to a fiscal year, being from
April to March (Apr, May, june being Q1)
And is there a way to have the year first folowed by the Qs?
Thank you