Skip to main content

I'm trying to do some math and get some totals of columns, but I have div/0 errors. 

The following formula calculates work days and prints the result from a "StartDate" and a "FinishDate" column. 

 
DATETIME_DIFF(EstFinish,EstStart,'days')-
(INT(DATETIME_DIFF(EstFinish,EstStart,'days')/7)+
IF(DATETIME_FORMAT(EstStart,'E')>DATETIME_FORMAT(EstFinish,'E'),1,0))*2+1
This formula works great, however when I do not yet have dates selected I cannot sum the values and having "ERROR" all over the view is kind of ugly. 

 

 


I know I could probably create a view that hides them then they wouldn't mess up the calculations but I'm wondering if there is a cleaner way to do this that will not throw an error if dates aren't selected? I know if this was Sheets I would simply do an IFERROR and I know that AT has ISERROR I just haven't been successful in figuring out how to use it. 

Any help would be greatly appreciated. Thank you!😁

Hey @Jarrod_Lilly! 

Give this a shot:

IF(
AND(
{EstFinish},
{EstStart}
),
DATETIME_DIFF(
{EstFinish},
{EstStart},
'days'
)
-
DATETIME_DIFF(
{EstFinish},
{EstStart},
'days'
) / 7
+
IF(
DATETIME_FORMAT(
{EstStart},
'E'
)
>
DATETIME_FORMAT(
{EstFinish},
'E'
),
1,
0
)
* 2 + 1
)

It's worth noting that I haven't had a chance to test this, but the only functional difference is that I've nested your original formula within an IF function that utilizes an AND function to evaluate whether both your date fields return data.
If that AND function returns true, then your original formula will be evaluated, otherwise the formula will return undefined, and therefore blank.

I removed the INT() function you had nested in your original formula, as the DATETIME_DIFF() function already returns whole integers.

Reply


Terms & Conditions