How to determine what period a DATE is in?
Posted by Mike on November 26, 2001 5:26 AM
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.