Nested If using Date

David Muskett

Hi all, I have looked through the discussions and cannot find the answer, if it is a previously answered question, my apologies.

 

I have three columns relevant to the question:

Due Date 

Status 

Completed Date

 

Status has conditional formatting, red, yellow and green corresponding to Awaiting resolution, overdue and completed, all basic stuff.

Just to add, conditional formatting is based on cell values from dropdown box, awaiting resolution, overdue, completed.

 

In Status I have this formula :

=IF([Due Date]4 < TODAY(), "Overdue", "Awaiting Resolution")

And it works perfectly.

 

I then change to this formula:

=IF(ISBLANK([Completed date]4), "Awaiting resolution", "Completed")

Which as a stand alone formula also works perfectly.

 

What I want to do is combine the formulas so that if due date is earlier than today, "awaiting resolution" but if "completed date has a value then "completed. I have tried this:

=IF([Due Date]4 < TODAY(), "Overdue", "Awaiting Resolution"), IF(ISBLANK([Completed date]4), "Awaiting resolution", "Completed")) and that fails!

 

So, let me try and put into words what I would like to acheive, mabe it helps you experts?

From the 3 columns above I want to populate "status" depending on comparing "due date" to Today().

However, if "Completed date" is a value (date), then that overrides the status and forces it to show "completed"

 

I have temporarily got around this buy having conditional format all the row green if the "date completed" has value. However, I would really like this at cell value as I have tried to describe.

 

Please help my frustration, thanks

 

Thanks

David 

 

 

 

Shaine Greenwood

Hi David,

 

Try this:

 

=IF(ISBLANK([Completed date]4), "Completed", IF([Due Date]4 < TODAY(), "Overdue", "Awaiting Resolution")))

 

When nesting IF statements, you nest one inside of another and close the parens at the very end of the formula.

 

IF Completed date is blank, return "Completed," IF Due Date is less than TODAY, return "Overdue," otherwise (if neither condition is met), return "Awaiting Resolution."

David Muskett

Thanks a million 

i will try ov the weekend and revert Monday 

Thanks 

David Muskett

#unparsable.

 

But let me delve a little to make sure it is not me!

 

Shaine Greenwood

I think I added an extra paren to the end of the formula by mistake, try deleting that and see what happens.

Mike Andreas

I answered a question very similar to this one on another thread.  Using my equations would require you to have slightly different columns than you mentioned here, but I think it would serve your needs.

David Muskett

Hi Shaine,

You were right, there was an additional parenthis and it now works but not quite as I want.

 

Let me try and descirbe:

If there is no data (date) in "completed date" then return "Awaiting resolution". However, if today() is greater than "due date" then return "Overdue". In otherwords, if the due date has passed.

If there is a date in "completed date" then that overrides "Overdue" or "awaiting resolution".

 

Thank you

David 

David Muskett

Dear Shaine,

 

I have fixed it:

 

=IF(NOT(ISBLANK([Completed date]4)), "Completed", IF([Due Date]4 < TODAY(), "Overdue", "Awaiting Resolution"))

 

Thanks to you and Mike. 

I have learned the subtilies of nesting If statements.

 

Kind regards,

David 

Shaine Greenwood

Glad you got it working!