Skip to Content
0
Former Member
Oct 30, 2007 at 04:22 PM

Comparing Records

10 Views

Post Author: Skip

CA Forum: Formula

I need to distinguish the number of incidents for a type of leave. Each entry for the ID is one day. Each incident can be up to 5 consecutive days. Great than 5 days counts as another incident.

I have a formula that compares and returns a value of 1 or 0 that I use in excel. I want to accomplish the same thing with a formula in Crystal.

Here is the Excel formula =IF(AND(A3=A2,B3-B2<5),0,1)

Here is the data

ID

Time Date

Sum Hours

01041

2006-04-19

11.25

1

01041

2006-04-20

11.25

0

01403

2006-06-28

11.25

1

01403

2006-06-29

11.25

0

01671

2006-04-26

7.5

1

01671

2006-04-27

7.5

0

01671

2006-04-28

7.5

0

01671

2006-09-18

7.5

1

01671

2006-09-19

7.5

0

01671

2006-09-20

7.5

0

01893

2007-03-14

7.5

1

01893

2007-03-15

7.5

0

01893

2007-03-16

7.5

0

Any help would be greatly appreciated.

Thanks