Help with date range conditional formula

**DMBerger** · 05-07-2009, 10:32 AM

Hello; I'm very new to working with formulas within Excel, and though I've done my best by perusing through the web, I've hit a stumbling block dealing with the following issue:

I have a number of names; each name has their beginning date and their ending date in adjacent columns (I'm in the military; so the first date is the day they joined, and the second date is when they are leaving the command). I have to track each person and determine which "zone" they are in. This is dependent on the range of time between their beginning date and end date. The zones are as follows:
Zone A: 0 to 6 years
Zone B: 6 years+1 day to 10 years
Zone C: >=10 years+1 day

My feeble attempts at an adequate formula have had issues, mostly centered around the fact that I can not get a formula that recognizes someone with exactly 6 or 10 years between their dates as being in the correct zone (A or B); usually it'll advance it to the next zone. An attempt to correct this gets that issue right, but other major issues erupted. Here's what I was working with (A3 is beginning date (example: 5/20/2000), B3 is ending date (example: 5/20/2010):

=IF(AND(DATEDIF(A3,B3,"Y")<=6,MONTH(A3)=MONTH(B3),DAY(A3)=DAY(B3)),"A",IF(AND(DATEDIF(A3,B3,"Y")<=10,MONTH(A3)=MONTH(B3),DAY(A3)=DAY(B3)),"B","C"))

The next one gets it almost right, but the aforementioned issue is the problem:

=IF(DATEDIF(A3,B3,"Y")<6,"A",IF(DATEDIF(A3,B3,"Y")<10,"B","C"))

Any help with this would be greatly appreciated! Oh, and while I've heard of VBA...orsomething, I have absolutely no knowledge of how to work with it. But any help is greatly appreciated.

**Special-K** · 05-07-2009, 11:09 AM

I have an idea.
Change the "y" to "d" to count the number of days. If it's greater than 10 * 365 then it's more than 10 years if it's greater than 6* 365 it's more than 6 years.

You'll need to calculate if there are any leap years between the dates and add an extra day for each leap year in between.
So for 2000 to 2010 Zone C would be 10 * 365 + 2 (2 leap years between 2000 and 2010).

How you'd go about easily determining a leap year I'm not sure at present apart from the obvious divide year by 4

**DMBerger** · 05-07-2009, 11:30 AM

I though about that, but 1) that leap year issue, which I think is not an issue when doing the =datedif "y" function, and 2) it's most important to get the days exactly correct. If said person is leaving 5/19/2010, and the formula hasn't counted the days exactly correct and it determines that he's in Zone C (and not the correct Zone B), then he'll get the wrong process done for him.