How to seperate several formulas data with similar conditions?

[Former Member]

Hi,

I have 3 formulas in the group footer where each row refers to each record.

Each row should show 0  0   1  or 1   0   0   or 0  1   0 but since some conditions are the same or the returned data is not accurate.

Is there a way to distinguish between formulas instead of adding more conditions to each formula?

IF ISNULL({END_DATE.date_value}) then '1' 

ELSE IF currentdate > {END_DATE.date_value} and Year({END_DATE.date_value}) = year(currentdate) then '1' 

ELSE '0'

IF ISNULL({END_DATE.date_value}) then '1' 

ELSE IF currentdate >= {START_DATE.date_value} and Year({START_DATE.date_value}) = year(currentdate) and currentdate <= {START_DATE.date_value} then '1' 

ELSE '0'

IF ISNULL({START_DATE.date_value}) then '1' 

ELSE IF currentdate < {START_DATE.date_value} and Year({START_DATE.date_value}) = year(currentdate) then '1' 

ELSE '0'

Regards,

B.

[abhilash_kumar]

Hi Barry,

Try:

IF

(

ISNULL({END_DATE.date_value}) OR

ISNULL({START_DATE.date_value}) OR

(currentdate IN [{START_DATE.date_value} TO {START_DATE.date_value}] AND Year({START_DATE.date_value}) = year(currentdate)) OR

(currentdate > {END_DATE.date_value} AND Year({END_DATE.date_value}) = year(currentdate)) OR

(currentdate < {START_DATE.date_value} AND Year({START_DATE.date_value}) = year(currentdate))

)

then '1'

Else '0'

-Abhilash

[Former Member]

Hi Abhilash,

Thanks for the formula but I need 3 separate formulas like I posted.

Take a look at records below: The 3 formulas are for the Completed, In Progress and Up Coming columns. There should be only 1 result appearing in each row (e.g. row 1, 3)

In Row 2 there are 2 values since in both formulas there is a condition (No End Date).

[abhilash_kumar]

Where do you display the '1' and '0' that these formulae output?

On Row2 which column should have the data and why?

-Abhilash

[Former Member]

Hi,

I managed to get the results needed for the moment so thank you for the help.

I have another question so I will open it in a new post

Barry.