How to Group Dates

Hello, I am working on a document and need some expertise.

I have a load of data, which is all sorted by date of occurances. However I need to be able to group these dates into 12 month sections, based upon the dates below but I am not sure how to do this?

What i need is the following:

All occurances between

30/06/2008 and 29/06/2009 - To return a value such as, Period 1
30/06/2009 and 29/06/2010 - Period 2
30/06/2010 and 29/06/2011 - Period 3

Is there a way I can do this using formula in my line by line data?

Thanks for any help and advice anyone can give, it will be greatly appreciated.

Hi,
you could park in the sheet a table like this (A2:B5)

30/06/2008 Period 1
30/06/2009 Period 2
30/06/2010 Period 3
30/06/2011 Period 4

And use a VLOOKUP

=VLOOKUP(date,$A$2:$B$5,2,TRUE)

I hope it's a little help.


Regards

H, Thanks for the quick response. I have kind of sussed it, but the middle part of my formula is wrong unfortunately...

I have created a table which is as follows:

Date Count Title
29/06/2008 1 Period 1
30/06/2008 2 Period 1
01/07/2008 3 Period 1
etc

In the title row, i have entered the following formula:

=IF(AZ737<266,"Period 1",IF(AZ737>365<731,"Period 2", IF(AZ737>730,"Period 3")))

The first part and final part work, insofar they return a value of Period 1 and Period 3.

However the middle section isn't working, it just returns "FALSE"

I could add on another column, which looks up the TITLE column and if it = Policy 1, return policy 1, if = FALSE, return Policy 2 etc but there must be an error in my formula, which I would like to correct.

thanks again

Hi just a modest attempt

=IF(AZ737<266,"Period 1",IF(AND(AZ737>365,AZ737<731),"Period 2", IF(AZ737>730,"Period 3")))

regards

Sorry, this is driving me nuts now

I have sorted out my table, so it groups each date within 365 days as Period 1, Period 2 and Period 3.

Now I need to add a column on, that looks at dates in my data and using the lookup, tells me which period that date is.

So in the line by line data for example, i will have an occurance date of 03/06/2009 which is in column K.

that will therefore be classed as period 1

What i need to do, is using my look up i need to return a value of Period 1 in another column, say column X.

What i tried was this, =IF(ISBLANK(K8),0,VLOOKUP(AY8:BA1102,3))

But this wont work, where have I gone wrong? the table is in date order so the look up should work, but it isn't liking the formula?

Originally Posted by CANAPONE

Hi just a modest attempt

=IF(AZ737<266,"Period 1",IF(AND(AZ737>365,AZ737<731),"Period 2", IF(AZ737>730,"Period 3")))

regards

Hi Canapone, thanks for your help with this, slowly but surely i'll get the hang of this