Countdown date Formula error

Platform discussions
1

Hello! I am building my board and make a formula column that can show countdown of workdays date info of the due date either from today date or completion date but there is a problem here to make it works.

This is my code
IF({Timeline#End}<>"",
IF({Completion Date}<>"",
IF(DAYS({Timeline#End},{Completion Date})>0,
“Complete - “&(WORKDAYS({Timeline#End},{Completion Date})-1)&” days”,
IF(DAYS({Completion Date},{Timeline#End})>0,
“Complete + “&WORKDAYS({Completion Date},{Timeline#End})-1&” days”,
“Complete 0 Days”
)
),
IF(DAYS({Timeline#End},TODAY())>0,
" - “&(WORKDAYS({Timeline#End},TODAY())-1)&” more days",
IF(DAYS(TODAY(),{Timeline#End})>0,
" + “&(WORKDAYS(TODAY(),{Timeline#End})-1)&” more days",
" 0 Days"
)
)
),
“-”
)

After several experiment to find where is the problem, then I conclude the problem appear at (WORKDAYS(xx,xx)-1) that can’t combined with that nested if. As an example

IF({Timeline#End}<>"",
IF({Completion Date}<>"",
IF(DAYS({Timeline#End},{Completion Date})>0,
WORKDAYS({Timeline#End},{Completion Date})-1,
IF(DAYS({Completion Date},{Timeline#End})>0,
“Complete + days”,
“Complete 0 Days”
)
),
“Simsalabim!”
),
“-”
)

“Simsalabim!” and “-” cant appear and the column only show one parameter is invalid

image

Does anyone have an idea to fix it? Or this is a bug?

2

@ilhamfajar,

The problem is basically due to the fact that the monday formula evaluator returns an error when any part of the formula is invalid, even when that portion is excluded. Specifically,
WORKDAYS({Timeline#End},{Completion Date})-1 returns an error whenever either of the dates is blank.

To fix it, you have to ensure that this part of the formula is always valid.

Try this:

IF({Timeline#End}<>"", IF({Completion Date}<>"", IF(DAYS({Timeline#End},{Completion Date})>0, WORKDAYS(LEFT({Timeline#End}&"2000-01-01",10), LEFT({Completion Date}&"2000-01-01",10))-1, IF(DAYS({Completion Date},{Timeline#End})>0, "Complete + days", "Complete 0 Days")), "Simsalabim!"), "-")
3

Ahh, Great!!! It’s work! Thanks a lot!

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.