Between 2 dates display x

**joebloggs21** · 03-22-2011, 06:14 AM

Hi all,

I have a column full of dates. I want to create a formula that looks at a cell in this column, and if it's between to certain dates then i want to display a value.

So for example: if the date in cell C4 is between 01/04/11 and 30/04/11, display 'Period 1'.

If its also possible i would like to add further sub functions, so using the above, if the date in cell C4 isn't between those 2 dates, but is between 01/05/11 and 28/05/11, display 'Period 2'.

Thanks in advance.

**teylyn** · 03-22-2011, 06:22 AM

Hello,

If you define a simple lookup table with the threshold dates for the periods, then the formula can be as simple as this:

=VLOOKUP(C1,$H$1:$I$3,2,TRUE)

See attached. The #N/A values in the first few rows mean that the date value is not found in the lookup table in columns H and I. The error value can be suppressed or replaced with a more meaningful message, if required.

cheers,

**joebloggs21** · 03-22-2011, 06:44 AM

Thank you. This seems to have worked only for Period 13 dates for some reason, the rest are displaying #N/A. I used the VLOOKUP table below:

14/11/2011 Period 9
12/12/2011 Period 10
09/01/2011 Period 11
06/02/2011 Period 12
06/03/2011 Period 13
01/04/2011 Period 1
01/05/2011 Period 2
29/05/2011 Period 3
26/06/2011 Period 4

I was only expecting results for Period 10-Period 13, but only Period 13 is showing.

Thanks.