Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Hiding Errors so I can do math.. Help :-)

Topic Labels: Formulas
600 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jarrod_Lilly
5 - Automation Enthusiast
5 - Automation Enthusiast

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. 
Jarrod_Lilly_0-1691427001234.png

 

 


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!😁
1 Reply 1

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.