Date search

[leehale]

Hi there,
I am trying to get a period reference from a list of dates, should be easy but I am struggling could you help please.
I have a list of dates going from 30/09/2009 going on through till 2011 they are dates entered when a trip is scheduled. I need to get them to referenced as a period, we have 3 periods 1 2 and 3 the dates for the periods are always the same and are as follows,
period 1 = 01/06/2009 till 31/12/2009
period 2 = 02/01/2009 till 31/03/2009
period 3 = 01/04/2009 till 31/05/2009
only the year changes but the dates stay the same. Sample below:

30/09/2009 Boarders Activities £2,045.00 PPC
21/12/2009 Burton Outing £440.00 PPC
17/12/2009 Roche Outing £380.00 PPC
06/10/2009 CCF £1,940.00 PPC
21/12/2009 Drama B O V £460.00 PPC
06/10/2009 Geography Trip £226.00 PPC
13/10/2009 History £148.00 PPC

Thanks in advance for taking a look.
Lee

 

[SkipVought]

Hi,
[tt]
period 1 is month 6 to 12
period 2 is month 1 to 3
period 3 is month 4 to 5
[/tt]
So if your date is in A1
[tt]
=if(month(A1)<4,3,if(month(a1)<6,2,1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[leehale]

Thanks for that good idea using just the months it nearly does what I want. If there is nothing in the date cell it defaults to 3 for some reason, can this be improved? sorry to be fussy but the guy using it will be happier having it right for him.

Thanks
Lee

 

[SkipVought]

Why would there be nothing in the cell, if you have a formula on that row?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[leehale]

Sorry to be late reply been out. The formula goes into A1 and the date is entered into A2, when I set the formula into A1 it returns period 3 when A2 is empty, not sure why but can manage if there is no option.

Thanks for your time
Lee

 

[SkipVought]

use an IF function and only execute the expression of there is a value in that cell (ISBLANK())

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}