IF formula with certain conditions to achieve a desired value

[Excel Dumbo]

Hello friends,

Please refer attached sample file.

I have dates in column A that do not appear in the date format , e
1.2013. 2.2013, 3.2013 etc

I have like to use a formula in another column B which will give me -

Q1 2013(which means quarter 1) for all dates that are 1.2013, 2.2013
3.2013
Q2 2013 (which means quarter 2) for all dates that are 4.2013
5.2013,6.2013
Q3 2013 (which means quarter 3) for all dates that are 7.2013
8.2013,9.2013
Q4 2013 (which means quarter 4) for all dates that are 10.2013
11.2013,12.2013

Thank

+-------------------------------------------------------------------
|Filename: Dates.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=944
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Wed, 12 Feb 2014 02:39:37 +0000 schrieb Excel Dumbo:

I have dates in column A that do not appear in the date format , eg
1.2013. 2.2013, 3.2013 etc

I have like to use a formula in another column B which will give me -

Q1 2013(which means quarter 1) for all dates that are 1.2013, 2.2013,
3.2013

in C2 try:
="Q"&ROUNDUP(INT(A2)/3,0) &" "&10000*MOD(A2,1)
and copy down


Regards
Claus B.

 

[Excel Dumbo]

thank you very much Claus. Formula worked perfec

+-------------------------------------------------------------------
+-------------------------------------------------------------------