Need to show if difference between two dates is more than 30 days (after or before)

[jonmagee]

Hello excel experts!

I'm trying to create a formula that will show me if two separate dates, have more than 30 days between them (whether it be 30 days before OR after), but disregarding the year. So for instance, in column B, line 2 (of the attached document), there's a date of 12/18/12, and in column C a date of 12/22/15. Because 12/18 is only 4 days before 12/22 (remember, we're disregarding the year), this information would be "correct", and wouldn't need to be marked.

I work for a non-profit and the info is for homeless agencies that have to do an annual followup assessments with their clients (within 30 days of client anniversary), so thanks for any help on this! It will be going to a good cause

[Special-K]

Try

in D2
=ABS(B2-DATE(YEAR(B2),MONTH(C2),DAY(C2)))
and copy down the column

NOTE: I haven't allowed for a leap year day that may occur if one of the dates is a leap year.

[JohnTopley]

Try

=IF(DATE(2016,MONTH(C2),DAY(C2))-DATE(2016,MONTH(B2),DAY(B2))<30,"Correct","")

I just used 2016 as the year to simplify the calculation.

.... just realised it will fail when dates fall over a year end.

Special-K 's approach better.

[jonmagee]

1st suggestion:
This sort of works, the only thing it's not doing is if the assessment date is BEFORE the enrollment date, it counts the difference the "long way", such as:

18189 1/4/2012 12:00 AM 12/22/2015 12:00 AM 353 Instead of "18".



2nd suggestion:
This doesn't work on some cases, such as:
16770 9/19/2013 12:00 AM 6/13/2016 12:00 AM Correct