MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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


Try extracting a number from each of your dates, indicating how many months they represent (since 1900); use the formulae


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
6 2
7 2

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

Not especially elegant, but it should work...



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

What about...


where PERIODEND is later in time than PUBDATE?

Would this aproximate computation fit your situation?