How to have a date range bring back a value... Help

[jkfrade]

Hello,

I am trying to creat a formula in which a value is brought back depending on a date range. For example, if a date listed in the workbook is between January 1 2011 to March 31 2011, I would want it to bring back Quarter 1, Quarter 2 if it is inbeteen the dates of April 1 and June 30, etc... and so on for the remainder of the year. I thought I could do this by a vlookup but it is not working. I also can not use VBA due to the type of workbook and others who will be viewing it. Any help would be more then greatly appreciated.

 

[fred]

=if(or(x>==40909,x<=40999),"Quarter1",if(or(x>=41000,x<=41090),"Quarter2",if(or(x>=41091,x< =41182),"Quarter3",if(or(x >=41183,x<=41274),"Quarter 4","")))))


X is the input date


if the result is blank that means you have entered a date that's outside of the calendar year 2012.

 

[jkfrade]

Thanks Fred, but I am not sure I completely understand your formula. I did get it to work by putting in a table on a seperate sheet and using the formula below:


=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)


D8 is my date, B is range 1 (first date for each quarter), C is range 2 (last date of each quarter) D is the value I want returned. It seems to be working... But I really won't know until I get it into the sheet that I need it for.

 

[Hui]

Have a look at

http://chandoo.org/wp/tag/group-by-quarter/

 

[prasaddn]

Hi JKFrade,


While suggestion given above works, i have one more easy formula to your situation. Assume you have your input date in C5 cell, put the below formula wherever you need to retreive the Qtrs..


=CHOOSE(MONTH C5), "Qtr1", "Qtr1", "Qtr1", "Qtr2", "Qtr2", "Qtr2", "Qtr3", "Qtr3", "Qtr3", "Qtr4", "Qtr4", "Qtr4")


Regards,

Prasad