Calculating DataDiff in Workingdays with blank values

Question

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 · Answer

Hi&nbsp;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&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

ronaldvdh · Answer

v-lionel-msft&nbsp;thanks for your help.&nbsp;The 2 tables have a relationship based on Date (in the Date Table) and de date in the column 'Binnenkomst' (in the Damage table)&nbsp;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&nbsp;Adres&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Binnenkomst&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Afgehandeld&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Calculated columnA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01-03-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03-03-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date Afgehandeld - Date BinnenkomstB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15-08-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31-10-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-11-19D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16-05-18&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16-08-18&nbsp;E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06-12-19&nbsp;Basically it needs to calculate that IF afgehandeld = BLANK() then calculated as if Afgehandeld is TODAY() and still returns an value based on workingdays&nbsp;

ronaldvdh · Answer

v-lionel-msft&nbsp;you have any ideas ?&nbsp;

v-lionel-msft · Answer

Hi&nbsp;RonaldvdH ,Is the result like this?DateDiff = 
IF(
    MAX([Afgehandeld   ]) &lt;&gt; BLANK(),
    DATEDIFF(
        MAX([Binnenkomst]),
        MAX([Afgehandeld   ]),
        DAY
    ),
    DATEDIFF(
        MAX([Binnenkomst]),
        TODAY(),
        DAY
    )
)&nbsp;Best regards,Lionel Chen&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ronaldvdh · Answer

v-lionel-msft&nbsp;thanks for the response 🙂&nbsp;&nbsp;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 ?

Forum Discussion

Calculating DataDiff in Workingdays with blank values

5 Replies

Related content

Datadiff returns wrong value

Diferença entre datas DataDiff

DATADIFF not recognised

DATADIFF as measure

MoM calculation