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
)
)
