Calculate a quarter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JPS - 18 May 2008 13:31 GMT

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,

Signature

JPS

Reply to this Message

Fred Smith - 18 May 2008 13:47 GMT

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

Regards,
Fred.

>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
[quoted text clipped - 11 lines]
>
> Thanks,

Reply to this Message

T. Valko - 18 May 2008 18:09 GMT

Get incorrect results:

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

Signature

Biff
Microsoft Excel MVP

> =TEXT(MONTH(A1)/4+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00")
>
[quoted text clipped - 17 lines]
>>
>> Thanks,

Reply to this Message

Fred Smith - 19 May 2008 01:52 GMT

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/

> Get incorrect results:
>
[quoted text clipped - 22 lines]
>>>
>>> Thanks,

Reply to this Message

Gary''s Student - 18 May 2008 13:55 GMT

With a date in A1:

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

Signature

Gary''s Student - gsnu200786

Reply to this Message

JPS - 18 May 2008 14:15 GMT

GSNU200786 & Fred

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

Signature

JPS

> With a date in A1:
>
> =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)

Reply to this Message

T. Valko - 18 May 2008 18:12 GMT

Here's another one:

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

Signature

Biff
Microsoft Excel MVP

>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
[quoted text clipped - 11 lines]
>
> Thanks,

Reply to this Message

Dana DeLouis - 19 May 2008 04:15 GMT

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

Signature

Dana DeLouis

>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
[quoted text clipped - 10 lines]
>
> Thanks,

Reply to this Message