IF/AND/OR formula with different return values
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.
Answers
-
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","")
-
Thank you for the assistance, unfortunately, neither statement works as I'm still receiving the #Unparseable error message.
-
@krinnap I think it's because you have different value types returned. Try removing the quotes around the 0 result.
-
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
Ipshita Mukherjee
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!