Challenge with dates

[Frick]

Here's my problem. I enter two dates, a start date and and end date. In
another area of the worksheet each col has a row with month date, 1st of
month and end of month. Now what I want to do is using the start and end
dates in the rows below each month is utilize a formula that does 6 things.

In each col month:
1. If the start date is between the 1st and end of month, enter CSD
2. If the start date is greater then the 1st and end of month, enter a
negative number that equals the number of months prior to the start date.
3. If the end date is between the 1st and end of month, enter ED
4. Between the CSD and ED enter C in those col's.
5. If the end date is less then the 1st and end of month, enter a positive
number that equals the number of months after the end date.
6. If all else fails, enter INVALID.

So here's what it should look like.

A1=Enter start date = 2/15/04
A2=End Date= 4/10/04

B1 through last col month (currently EE1) 1st of month. B1=1/1/04, C1
forward=B2+1
B2 through last col month (currently EE2) last day of month. Formula
EOMONTH(B1,0)

Rows 5 through 20 is the capture range.

Example:
Start Date 2/15/04
End Date 4/10/04

B C D E
F
Row 1 1/1/04 2/1/04 3/1/04 4/1/04 5/1/04
Row 2 1/31/04 2/27/04 3/31/04 4/30/04 5/31/04

Row 5 -1 CSD C ED
1

While this example is simplistic, the actual range between start and end
dates may be greater then 12 months..

Hope someone can help with a simple short formula.

Thanks.

 

[Frank Kabel]

Hi
not fully tested but try the following formula in B5
=IF(MONTH($A$1)>MONTH(B2),MONTH(B2)-MONTH($A$1),IF(MONTH
($A$1)=MONTH(B2),"CSD",IF(AND(MONTH($A$1)<MONTH(B2),MONTH
($A$2)>MONTH(B2),"C",IF(MONTH($A$2)=MONTH(B2),"ED",IF(MONTH
($A$2)<MONTH(B2),MONTH(B2)-MONTH($A$2),"Invalid")))))