Calculating DataDiff in Workingdays with blank values

[RonaldvdH]

Goodday brainiacs, i'm in need of your help once again

I want to calculate the average time (in workingdays) it takes to fix a complaint or a damage created by our teams

I have a date table with workingdays in it and another table called damages with colums such as 'Binnengekomen' (the date on which the damages has been registered) and 'Afgehandeld' (the date on with the damage has been fixed)

So i need a formula to calculate the datediff in workingdays between both dates but the problem starts when a damage has been registrered but not yet solved, so i have blank values, and then the formula should calculate with TODAY()

can anyone help me with this formula ?

[v-lionel-msft]

Hi @RonaldvdH ,

I tried to create two tables that you described, but the information you gave is too little. I can’t create relationship between the two tables.

Maybe you can give me more columns , then, I can make a data model and calculate the dateDiff in workingdays.

Best regards,
Lionel Chen

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

[RonaldvdH]

@v-lionel-msft thanks for your help.

The 2 tables have a relationship based on Date (in the Date Table) and de date in the column 'Binnenkomst' (in the Damage table)

The damage table consists of addresses and dates when a damage is registrered (column 'Binnenkomst') and when it's solved (column 'Afgehandeld')but also a damage type and category.
The Date table consists of Date, weekday, isWorkday, DayOfWeek and Week number

Adres           Binnenkomst           Afgehandeld          Calculated column

A                  01-03-19                 03-03-19                Date Afgehandeld - Date Binnenkomst

B                  15-08-19             

C                  31-10-19                 01-11-19

D                  16-05-18                 16-08-18 

E                   06-12-19

Basically it needs to calculate that IF afgehandeld = BLANK() then calculated as if Afgehandeld is TODAY() and still returns an value based on workingdays

[v-lionel-msft]

Hi @RonaldvdH ,

Is the result like this?

DateDiff = 
IF(
    MAX([Afgehandeld   ]) <> BLANK(),
    DATEDIFF(
        MAX([Binnenkomst]),
        MAX([Afgehandeld   ]),
        DAY
    ),
    DATEDIFF(
        MAX([Binnenkomst]),
        TODAY(),
        DAY
    )
)

Best regards,
Lionel Chen

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

[RonaldvdH]

@v-lionel-msft thanks for the response 🙂 

However the result now is that every value is 0,00 and that can't be correct but i feel that is should work.

Both column are in the same table but i need to calculated based on working days so there should be a relation with de DATE table right ?

[RonaldvdH]

@v-lionel-msft you have any ideas ?