Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Nested If using Date

David Muskett
edited 12/09/19 in Archived 2017 Posts

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 


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









This discussion has been closed.