Error in IF formula for counting days

I’ve this formula to determine the project days and its based on the status of the project. If project status is commenced I need to count the days between the {Intro Call Date} and {Commencement Date}. Else it is difference between {Intro Call Date} and Today ().

Getting the error one of the parameter is invalid on items where the status is not “Commenced”.
35 is the KPI days, so the project days increments from -35.

What am I doing wrong.

IF({Commencement Status}="Commenced",ROUNDDOWN(DAYS({Commencement Date},{Intro Call Date}),0)-35,ROUNDDOWN(DAYS(Today(),{Intro Call Date}),0)-35)


It’s not you.

The problem is that the first DAYS() function is ALWAYS being evaluated, even if not needed. But it doesn’t always have a valid date.

Try this:

IF({Commencement Status}="Commenced",
   ROUNDDOWN(DAYS(IF({Commencement Date}, {Commencement Date}, "1/1/1"), {Intro Call Date}), 0) - 35,
   ROUNDDOWN(DAYS(Today(), {Intro Call Date}), 0) - 35

Also, as a general rule, I would recommend using FORMAT_DATE() instead of ROUNDDOWN() in these types of date formulas (for any dates within the last 100 years). (it’s just more correcter :grin:)

IF({Commencement Status}="Commenced",
   DAYS(FORMAT_DATE(IF({Commencement Date}, {Commencement Date}, "1/1/1")), FORMAT_DATE({Intro Call Date})) - 35,
   DAYS(FORMAT_DATE(Today()), FORMAT_DATE({Intro Call Date})) - 35

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session

1 Like

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