Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

[teflonreis]

Hello everyone

I have two columns with dates. And have added a calculated date column that gives me the difference between the the dates in working days. So far so good.

CalculatedColumn	date1	date2
0
0		5/23/2022
0
0
-2	5/23/2022
0
0
2	5/30/2022	6/1/2022
0

However you probably noticed the problem. The calculated column magically makes up differences when there are no data or one date missing. My two columns have lots of empy cells. In case of two empty cells, calculated columns gives me 0-0=0 days.

In case of only one date and one empty cell, if even gives me negatives number of days.

I fail at adding a condition such that it only outputs the difference if there really are two date dates.

Since I want to compute the average number of working days in a Report, I get a wrong average (negative number of days).

Really am stuck and would appreciate some help. 

This is the condition I am thinking about. However never used variables before.

IF (AND(Table[date1]<>0, Table[date2]<>0), VAR CalculatedColumn, "Error or do nothing and dont go into average measure")

And this is the CalculatedColumn formula that I currently use. I would make it a VAR Variable and add an IF statement. Again, never done that before.

CalculatedColumn =
( DATEDIFF ( Table[date1], Table[date2], DAY ) )
    - INT ( DATEDIFF ( Table[date1], Table[date2], DAY ) / 7 ) * 2
    - IF (
        WEEKDAY ( Table[date2] ) < WEEKDAY ( Table[date1] ),
        2,
        IF (
            OR (
                WEEKDAY ( Table[date2] ) = 7,
                WEEKDAY ( Table[date1] ) = 1
            ),
            1,
            0
        )
    )

[tamerj1 · Accepted solution]

Hi @teflonreis 
Replace 

AND(Table[date1]<>0, Table[date2]<>0)

with

AND(Table[date1 > 0, Table[date2] > 0)

[tamerj1 · Accepted solution]

Hi @teflonreis 
Replace 

AND(Table[date1]<>0, Table[date2]<>0)

with

AND(Table[date1 > 0, Table[date2] > 0)

[teflonreis]

Hate to be the guy that states the error codes:

Error "Expressions that yield variant data-type annot be used to define calculated columns.

Am I on the right path? Or was it a mistake to use variables in a calculated columns to begin with.

[tamerj1 · Accepted solution]

@teflonreis 
Just delete the  , ""

[teflonreis]

Oh that fixed it. Sorry for the stupid questions. 

Many many thanks for the patience and help.