IF Function not giving correct value!

Mindfull

I have a scenario where I need to capture all as per the end date and the date request by the requestor to complete the task, are completed on time. I wrote the following formula basis that.

=IFERROR(IF([Work Order Status]@row = "On Hold", 1, IF([Project End Date]@row < [Requested Date]@row, 1, IF([Project End Date]@row < [Revised Request Date]@row, 1, 0))), "")

Even though I have added IFERROR for the blank cells it is returning 1 as the value in the formulae.

Can anyone please help with this? 

Paul Newcome

A blank cell will always be considered less than a cell with a date in it. It isn't necessarily throwing an error, so the IFERROR won't kick in.

Which cell(s) being blank should produce a blank value from the formula?

Mindfull

https://community.smartsheet.com/discussion/comment/284943#Comment_284943

It is returning "1" for blank Project End Date. Can you please help me to re-write this?

Prajna

Bassam Khalil

Hi @Prajna J

Hope you are fine, try to use IF with ISBLANK function to avoid blank cells.

Paul Newcome

Try this...

=IF([Project End Date]@row <> "", IF(OR([Work Order Status]@row = "On Hold", [Project End Date]@row < [Requested Date]@row, [Project End Date]@row <[Revised Request Date]@row), 1))

Mindfull

https://community.smartsheet.com/discussion/comment/284963#Comment_284963

Paul thanks for working on this, but it is still not working.

It should return "1" only in the case where Project End Date < Request Date and Revised Request Date and when the status of the task is "On Hold". it should also leave the formula cell blank if any of the reference cells is blank.

Prajna

Paul Newcome

OK. That is different from how I was understanding your initial post and comments. If ALL must be filled in and ALL must be true, then you would use something like this...

=IF(AND([Project End Date]@row <> "", [Work Order Status]@row = "On Hold", [Requested Date]@row <> "", [Revised Request Date]@row <> "", [Project End Date]@row < [Requested Date]@row, [Project End Date]@row < [Revised Request Date]@row), 1)

Mindfull

https://community.smartsheet.com/discussion/comment/284978#Comment_284978

it is still not working and leaving the cell blank without value.

Mindfull

In the same sequence, I have one more formula, I have done one more formula wherein I need to know what all tasks are delivered on committed date .i.e., the project end date is the same as the commit date. I tried following but not giving any value.

=IF(AND([Project End Date]@row <> "", [Work Order Status]@row = "On Hold", [Commit Date]@row <> "", [Project End Date]@row < [ Commit Date]@row), 1)

Prajna

Mindfull

https://community.smartsheet.com/discussion/comment/284950#Comment_284950

Hi,

If I use ISBLANK it gives #INCORRECT ARGUMENT SET Error.

Paul Newcome

=IF(AND([Project End Date]@row <> "", [Work Order Status]@row = "On Hold", [Requested Date]@row <> "", [Revised Request Date]@row <> "", [Project End Date]@row < [Requested Date]@row, [Project End Date]@row < [Revised Request Date]@row), 1)

This formula says that none of the three fields can be blank, [Work Order Status] must be "On Hold" and [Project End Date] must be less that both the [Requested Date] and the [Revised Request Date].

This is based on your comment of

"It should return "1" only in the case where Project End Date < Request Date and Revised Request Date and when the status of the task is "On Hold". it should also leave the formula cell blank if any of the reference cells is blank."

Kelly Moore

Hey @Prajna J

Since you're making a Less than, Greater than comparison with dates, you could try the ISDATE function to only allow Date values to be evaluated. This function will also weed out any blanks that might be present

=IF(AND(ISDATE([Project End Date]@row), [Work Order Status]@row = "On Hold", ISDATE([Commit Date]@row), [Project End Date]@row < [Commit Date]@row), 1)

Kelly