How to determine what period a DATE is in?

Posted by Mike on November 26, 2001 5:26 AM

Hello,

I need a little help with the following problem.

I have a sheet which has a cell with a date called PERIODEND, and for this example lets say it's 30/6/2001.
What i need to do is write a formula that will tell me if another date called PUBDATE is in either of the four preceding six-month periods. Then i need the formula to return a 1 if PUBDATE is in the preceeding six months, 2 if between six and twelve months prior, 3 if between twelve and eighteen months prior, and 4 if between eighteen and twenty-four months prior. Any dates older than this do not necessarily need the no. of six month periods calculated.

I am having trouble due to the fact that each year the no of days in a six months period is fairly variable, so I couldn't use the simple solution.

Any help would be greatly appreciated.

Regards

Posted by Ian Bartlett on November 26, 2001 7:05 AM

Mike,

Try extracting a number from each of your dates, indicating how many months they represent (since 1900); use the formulae
=YEAR(PERIODEND)*12+MONTH(PERIODEND)

and
=YEAR(PUBDATE)*12+MONTH(PUBDATE)

Once you have these two numbers, subtract the PUBDATE number from the PERIODEND number, leaving the difference in months.

Make a table of numbers from (say) 1 to 24, and the corresponding numbers you want returned:

1 1
2 1
(etc)
6 2
7 2
(etc)

Use VLOOKUP to convert the difference in months into 1, 2 etc.

Not especially elegant, but it should work...

Regards,

Ian

Posted by Aladin Akyurek on November 26, 2001 9:24 AM

What about...

=ROUND(DATEDIF(PUBDATE,PERIODEND,"M")/6,0)

where PERIODEND is later in time than PUBDATE?

Would this aproximate computation fit your situation?

Aladin

===========