Forum Discussion

teflonreis's avatar
teflonreis
Frequent Visitor
4 years ago
Solved

Remove null in a Calculated Date column

Hello everyone   I have a newly set up list or table with little data so far. Inside it, there is a calculated date column that sadly displays 0 although no dates were given. Looks like this in Q...
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi teflonreis 

    >> only if both dates are given, it calculates the difference. Else does not calculate anything.

    Please add if judgment before your calculation statement, 

    IF('Table'[OrderDate]<>BLANK() && 'Table'[DeliveryDate]<>BLANK(), your expression)

    e.g.

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi teflonreis 
Please try

 

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

By the way, what do you mean by this condition?