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

Tracey wrote:
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()T argetDate,"Overdue","Open")))

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

Ulrike