IF statement help

[Tracey]

Hi

I'm hoping for some help with a long IF statement (welcome any alternatives)

I have 4 dates:

Open Date (D1), Target Date(D2), Revised Date (D3) and Closed Date (D4).

What I am wanting to do is create a Status(S1) of either "Open" "Closed" or
"Overdue" based on the dates...

So if it was opened on 01-Mar-09 with a target date of 15-Mar-09 and based
on todays date it is "Overdue" but if theres a revised date of 25-Mar-09 it
is "Open".
And if there is a closed date its "Closed".

The formula I have which excludes the Revised date is as follows:

=IF(D4<>"","Closed",IF(Today()<D2,"Open",IF(Today()>D2,"Overdue",IF(AND(D2=0,Today()=0),"",""))))

Any help would be appreciated

Many thanks

 

[Ulrike]

Hi

I'm hoping for some help with a long IF statement (welcome any alternatives)

I have 4 dates:

Open Date (D1), Target Date(D2), Revised Date (D3) and Closed Date (D4).

What I am wanting to do is create a Status(S1) of either "Open" "Closed" or
"Overdue" based on the dates...

So if it was opened on 01-Mar-09 with a target date of 15-Mar-09 and based
on todays date it is "Overdue" but if theres a revised date of 25-Mar-09 it
is "Open".
And if there is a closed date its "Closed".

The formula I have which excludes the Revised date is as follows:

=IF(D4<>"","Closed",IF(Today()<D2,"Open",IF(Today()>D2,"Overdue",IF(AND(D2=0,Today()=0),"",""))))

Any help would be appreciated

Many thanks

Try this formula:

=IF(ClosedDate<>"","Closed",IF(RevisedDate<>"",IF(TODAY()>RevisedDate,"Overdue","Open"),IF(TODAY()>TargetDate,"Overdue","Open")))

Replace ClosedDate, RevisedDate, TargetDate with the respective cell
references.

Ulrike