Solved

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

Posted on 2011-02-10
5
195 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
Comment Utility
=IF(C2<TODAY()-60;1;"")
0
 
LVL 23

Accepted Solution

by:
Brian B earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now