Solved

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

Posted on 2011-02-10
5
214 Views
Last Modified: 2012-05-11
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
Comment
Question by:morinia
5 Comments
 
LVL 5

Expert Comment

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

Accepted Solution

by:
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

by:jppinto
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

by:Alan3285
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

by:Alan3285
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question