Checking dates to see if they are less than 60 days apart - Excel 2007

I have a file which is sorted my ID.  I would like to check each ID to see if the member has any claims within 60 days of the prior claim (same date does not count),  If they do  I would like to put a "1" in column  "F".  Attached is a sample file.

Is there a way I can do this?
Ck-Frequency.xlsx
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
Assuming the list will always be sorted by claim, column F would go like this, starting in the second row:

=if(A1=A2,if(C2-C1<60,1,""),"")

0
 
skrgaCommented:
=IF(C2<TODAY()-60;1;"")
0
 
jppintoCommented:
Please chec k the attached file to see if this is waht you want.

jppinto
Ck-Frequency-1-.xlsx
0
 
AlanConsultantCommented:
Hi,

See attached.

I changed the date in the yellow cell to demonstrate it putting a 1 in col F.

Alan.
Ck-Frequency---Version-1.xlsx
0
 
AlanConsultantCommented:
Hi,

Sorry, forgot to mention that the formula in col F is an array formula.

When editing it, you need to re-enter it with Shift-Ctrl-Enter (but don't type in the {} braces - excel adds those when you do Shift-Ctrl-Enter.

Alan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.