Forum Discussion

lennox25's avatar
lennox25
Post 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
Post Patron
4 years ago

Greg_Deckler Thanks, great, but what about factoring in the blank end dates?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

lennox25 VAR __EndDate = IF(ISBLANK(...), TODAY(), ...). 

 

Basically just substitute today's date if it is blank.

  • lennox25's avatar
    lennox25
    Post Patron
    4 years ago

    Greg_Deckler Hi - I know I may sound dull.. Im still learning.  In Network Days - How do I change the formula to work for me? What is calendar  and calendar 2 and what [Date] field do I use? Thank you

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

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      lennox25 Try something like this:

      NetWorkDays = 
VAR __StartDate = MAX(NetWorkDays[created date])
VAR __EndDate = MAX(NetWorkDays[review date])
VAR __EndDate1 = IF(__EndDate <> BLANK(), __EndDate, TODAY())
VAR Calendar1 = CALENDAR(__StartDate , __EndDate1 )
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
      • lennox25's avatar
        lennox25
        Post Patron
        4 years ago

        Greg_Deckler Thank you - Ive used the fomula and its throwing up all 3s?