Solved

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

Posted on 2011-02-10
219 Views
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
0
Question by:morinia

LVL 5

Expert Comment

ID: 34865662
=IF(C2<TODAY()-60;1;"")
0

LVL 23

Accepted Solution

Brian B earned 250 total points
ID: 34865718
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

LVL 33

Assisted Solution

jppinto earned 250 total points
ID: 34865728
Please chec k the attached file to see if this is waht you want.

jppinto
Ck-Frequency-1-.xlsx
0

LVL 12

Expert Comment

ID: 34865775
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

LVL 12

Expert Comment

ID: 34865791
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directiâ€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.