This may be a shot in the dark and will be a long formula, but here it goes:

I have multiple columns that I want to condense to one. Therefore, I want
column B to show current status of a work order based on the following:

If column V is blank, column B shows €œOpen€�.

If column V is filled (date) and column J K, column B shows €œComplete/No€�.
If column J <= K, column B shows €œComplete/Yes€�.

Now even though column V is filled, once column AE is filled (date) and
column J K, column B shows €œClosed/No€�. If column J <= K, column B shows
€œClosed/Yes€�.

If column AC and AE are filled (date), column B shows €œClosed/BER€�.

If column AD and AE are filled (date), column B shows €œCanceled€�.

Try this out in cell B2 (assumes other data is on row 2):

=IF(V2="","Open",
IF(AND(AD2<"",AE2<""),"Cancelled",
IF(AND(AC2<"",AE2<""),"Closed/BER",
IF(AE2<"",
IF(J2K2,"Closed/No","Closed/Yes"),
IF(J2K2,"Complete/No","Complete/Yes")))))

This is all one formula - I've just split it at each IF to make it
easier to read. It doesn't test for dates specifically, just whether
the cells are empty or not. Copy down if it meets the criteria.

Pete

Thanks. That was exactly what I needed.

One more thing I need though, I forgot to add that K may have N/A entered
sometimes. This would result in a "Closed/NA" or "Completed/NA" in B2.

Can you help one more time?

"Pete" wrote:

Try this out in cell B2 (assumes other data is on row 2):

=IF(V2="","Open",
IF(AND(AD2<"",AE2<""),"Cancelled",
IF(AND(AC2<"",AE2<""),"Closed/BER",
IF(AE2<"",
IF(J2K2,"Closed/No","Closed/Yes"),
IF(J2K2,"Complete/No","Complete/Yes")))))

This is all one formula - I've just split it at each IF to make it
easier to read. It doesn't test for dates specifically, just whether
the cells are empty or not. Copy down if it meets the criteria.

Pete

Try this out in B2 (all one formula, as before):

=IF(V2="","Open",
IF(AND(AD2<"",AE2<""),"Cancelled",
IF(AND(AC2<"",AE2<""),"Closed/BER",
IF(AE2<"",
IF(K2="N/A","Closed/NA",
IF(J2K2,"Closed/No","Closed/Yes")),
IF(K2="N/A","Completed/NA",
IF(J2K2,"Completed/No","Completed/Yes"))))))

It might be better to insert a new column C (assuming you are using C
at the moment), then you can split this formula so that the words
"Open", "Cancelled", "Closed" and "Completed" appear in column B as
appropriate and the words "BER", "NA", "No" and "Yes" appear in column
C (right aligned).

Pete

Thanks again and for the recommendation.

"Pete" wrote:

Try this out in B2 (all one formula, as before):

=IF(V2="","Open",
IF(AND(AD2<"",AE2<""),"Cancelled",
IF(AND(AC2<"",AE2<""),"Closed/BER",
IF(AE2<"",
IF(K2="N/A","Closed/NA",
IF(J2K2,"Closed/No","Closed/Yes")),
IF(K2="N/A","Completed/NA",
IF(J2K2,"Completed/No","Completed/Yes"))))))

It might be better to insert a new column C (assuming you are using C
at the moment), then you can split this formula so that the words
"Open", "Cancelled", "Closed" and "Completed" appear in column B as
appropriate and the words "BER", "NA", "No" and "Yes" appear in column
C (right aligned).

Pete