Forum Discussion

lennox25's avatar
lennox25
Icon for Post Patron rankPost Patron
4 years ago
Solved

How to calculate date difference excluding weekends - Always a start date but some end dates blank

Hi - I wonder if anyone can help. I need to calculate the date difference excluding weekends. There is always a start date but sometimes the task has not been completed so there is no end date.   B...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi lennox25 ,

    I have created a simple sample, please refer to it to see if it helps you.

    Replace a blank date with today's date.

    Click Trandform data>>Trandform>>Replace values.

    Then change the column.

    = Table.ReplaceValue(#"Changed Type",null,DateTime.LocalNow() ,Replacer.ReplaceValue,{"end date"})

    Then the null value will  be filled with present time.

    Then create a measure by using Greg_Deckler 's Net Work Days .

    NetWorkDays = 
VAR Calendar1 = CALENDAR(MAX(Sheet3[start date]),max(Sheet3[end date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

    If I have misunderstood your meaning, please provide some sample data and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

  • lennox25's avatar
    lennox25
    Icon for Post Patron rankPost Patron
    4 years ago

    HI amitchandak I already have differnce between the dates as formua shown in my original question. The formula you provided works perfectly providing there are no blank dates. There are blank dates (end dates) so now the formula shows as error. I have a standard (company) date table and have brought in an excel spreadsheet that gets updated daily  -which contains all the start and end dates.