Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DATEDIFF without weekends still doesn't work...

Hello everyone,

 

I have read a lot of solutions about how to exlude weekends in a datediff formula, but i did not work for me.

My formula is:

IF([Order uitgewerkt] = "Yes",
IF(
DATEDIFF([Order in],[Engineering ready],DAY)<0,
0,
DATEDIFF([Order in],[Start engineering],DAY)-1),
DATEDIFF([Order in],TODAY(),DAY)-1)

 

in al the three DATEDIFF i need to exlude weekends.

 amitchandak replied a few days ago with this answer how to solve this: COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)

but this doesn't work for me because some of my date rows are empty so the formula gives a error that CALENDAR can't be empty and i don't kwow how to solve this

 

I already have a column that says if a day is a workday or a weekend. I tried to put in a filter before the datediff but that doesn't work either.

 

Anyone knows how to solve this? i tried everyting i could for a week now but i can't get the right formula that exclude weekends unfortunately..

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , If you these two columns in one table and you date table with workday(1/0)

    A new column =

    sumx(filter('Date', 'Date'[Date] >= Table[Start engineering] && 'Date'[Date] >= Table[Engineering ready]),[Work Day])

     

    Refer to file below, Where on 2nd page work day diff as measure form 2 tables. But same is true for one able

     

    Also discussed it here, if this can help: How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA