IF/AND/OR formula with different return values

krinnap

I have created the following formula to calculate the remaining duration of a task:

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), NETWORKDAYS(TODAY(),[End Date]@row), 0)

I would like to be able to add an additional components that allows me to return different values if the Start Date and End Date are in the past. This is what I drafted that is getting an #UNPARSEABLE error:

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), NETWORKDAYS(TODAY(),[End Date]@row), 0), IF(AND([Start Date]@row<TODAY(), [End Date]@row<TODAY), "0","")

The original formula above works fine, it's just the additional IF statement that is erroring out. I was thinking it should be an OR statement but the return values would be different.

Thank you in advance for your assistance.

Lucas Rayala

You have two formulas in the same line, separated by a comma. Two options:

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), NETWORKDAYS(TODAY(),[End Date]@row), IF(AND([Start Date]@row<TODAY(), [End Date]@row<TODAY), "0",""))

or

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), NETWORKDAYS(TODAY(),[End Date]@row), 0) + IF(AND([Start Date]@row<TODAY(), [End Date]@row<TODAY), "0","")

krinnap

Thank you for the assistance, unfortunately, neither statement works as I'm still receiving the #Unparseable error message.

sharkasits

@krinnap I think it's because you have different value types returned. Try removing the quotes around the 0 result.

Ipshita

Hello @krinnap ,

All of the formula above is wrong except the one you specified at first, as the TODAY at the end is not wrapped in (), the 0 should not be in quotes "" and the formula does not make any sense.

If I understand correctly, you are trying to calculate the duration of a task from start to end, as well as the remaining duration of a task if the end date is past today and falls on any date in the future and so, logically if both your start and end dates are in the past, then the calculated duration for the latter part would always return 0 because the task is now considered "complete"

Here is how I tested out your scenario -

In the above test, the remaining duration is automatically returning 0 days when the end date is in the past and 1 if the end date is today.

Hope this helps.

Cheers! :)

Ipshita

krinnap

Thank you. I was trying to avoid a lot of columns with zeros where they weren't needed, but this will provide the value I need for the condition I'm trying to satisfy.