Need to remove unwanted Values! 2

[thi123]

I was calculating the number of days based on the current and previous dates. Previously, I got a tip from synapsevampire for the formula I used to removed the unwanted dates and calculate the number of days correctly, however I still have this value that I cannot git rid of.

Here’s the sample result:
OrdDate ActDesc FinDate #ofDays
8/2/2002 FrmDelvd 11/2/02 90
8/2/2002 SpecCompl 1/2/03 60
8/2/2002 InsTst 1/15/03 13
8/2/2002 InsCompl 2/15/03 30

4/5/2003 FrmDelvd 1/1/1970 -
4/25/2003 SpecCompl 1/1/1970 -
4/25/2003 IntTst 5/13/2003 12185
4/25/2003 IntInst 1/1/1970

7/24/2002 FrmDelvd 1/1/1970 -
7/24/2002 SpecCompl 9/23/2003 12318
7/24/2002 IntTst 11/19/2003 57
7/24/2002 IntInst 12/29/2003 20

I am calculating the number of days when the order was completed using the previous function. In my report, I prevented the 1/1/1970 from displaying since this is a null date so the wrong value will not display, however the next date 5/13/2003 are still calculating from the previous date 1/1/1970 which resulted a value of 12185.

What I would like to accomplish is to remove this wrong values if the previous date is NULL or equal to 1/1/1970. Is there a simple formula to do this? Thanks in advance.

 

[bdreed35]

Can you post your formula that you are using?

You probably need to check for the previous date being equal to 1/1/1970 but I can't confirm that until I see what you are doing currently.

~Brian

 

[lbass]

You should also explain what value you want to use in the formula instead of the previous value if the previous value = 1/1/1970.

-LB

 

[thi123]

Thanks for the quick reply guys. Here's the formula I used to calculate the # of days field.

if {#CntGrpTotal} = 1
and
not(isnull({Table.FinDate}))
and {Table.FinDate} <> datetime(1970,1,1,00,00,00)
then
totext({Table.FinDate}-{Table.OrdDate})
else
if {Table.FinDate} = datetime(1970,1,1,00,00,00)
then "" else
totext({Table.FinDate}-previous({Table.FinDate}))

 

[thi123]

LB,

Can you explain further what you mean to "also explain what value you want to use in the formula instead of the previous value if the previous value = 1/1/1970."? Sorry, I just don't get it. Waiting...

 

[lbass]

I meant: What should the incorrect values actually be? Do you want them to be blank or to be some other value, e.g., they could default to {table.FinDate} - {table.OrdDate}? It looks like you want them to be blank, so then try:

if {#CntGrpTotal} = 1
and
not(isnull({Table.FinDate}))
and {Table.FinDate} <> datetime(1970,1,1,00,00,00)
then
totext({Table.FinDate}-{Table.OrdDate})
else
if ({Table.FinDate} = datetime(1970,1,1,00,00,00) or
previous({table.FinDate}) = datetime(1970,1,1,00,00,00))
then "" else
totext({Table.FinDate}-previous({Table.FinDate}))

-LB

 

[thi123]

LB,

It worked....I'm such an idiot. I've been trying the same formula but I am using or previous({table.FinDate}) = " " instead of previous({table.FinDate}) = datetime(1970,1,1,00,00,00)) that's why.

Thanks.. your awesome!!!