Calculate immediate values

[SBpsc]

Using Crystal XI and MySQL db.

I have the following info available to me:

Date1 | Date2 | Info1 Formula
==============================
02/02/06 | 12/12/05 | a 0
02/02/06 | 01/16/06 | b 1
02/02/06 | 04/04/06 | c 1
02/02/06 | 05/03/06 | d 0
...

I need to create a formula that will display '1' if Date2 is immediately before and after Date1. Can anyone tell me how to do this?

 

[lbass]

You are going to have to explain what you mean by "immediate", as your example does not clarify this.

-LB

 

[SBpsc]

Sorry about that...

Let me try again. I need to compare Date2 and Date1.

01/16/06 and 12/12/05 are earlier dates than Date1, however, 01/16/06 is closer to Date1 than 12/12/05, so 01/16/06 would be the immediate before value.

04/04/06 and 05/03/06 are later dates than Date1, however, 04/04/06 is closer to Date1 than 05/03/06, so 04/04/06 would be the immediate after value.

I need to display a '1' for the immediate before and after values and '0' for everything else.

I hope I did not make this confusing...

Thanks in advance.

 

[lbass]

Much better. Create a formula {@datediff}:

datediff("d",{table.date1},{table.date2})

Then create a second formula {@closest}:

if (
{@datediff} < 0 and
(
next({@datediff}) >= 0 or
next({table.date11}) <> {table.date1}
) or
(
{@datediff} > 0 and
(
previous({@datediff}) <= 0 or
(previous{{table.date1}) <> {table.date1}
)
) then 1

-LB

 

[SBpsc]

Had to add a couple of missing parantheses and then it worked.

Thanks so much.