Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago

Calculating DataDiff in Workingdays with blank values

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...
Need Help
RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago

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's avatar
v-lionel-msft
Community Support
6 years ago

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's avatar
    RonaldvdH
    Post Patron
    6 years ago

    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 ?