Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

NETWORK DAYS

Hi community, On Powerquery, I have two columns containing a start and end date, but I will like to  calculate the number of working days, in a custom column.  I would really appreciate any help w...
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Anonymous  , Try using this method 

     

    1. Open the Power Query Editor.

    2. Select the table that contains your start and end date columns.

    3. Go to the "Add Column" tab and click on "Custom Column."

    4. In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").

    5. Use the following formula to calculate the working days:

       

      M
      WorkingDays = List.Difference( List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0)), List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0), #duration(1, 0, 0, -1)), (a, b) => Date.ToText(a) = Date.ToText(b) ), WorkingDaysCount = List.Count(WorkingDays)

       

       

      Please accept as solution if it helps 

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

 , Try using this method 

 

  1. Open the Power Query Editor.

  2. Select the table that contains your start and end date columns.

  3. Go to the "Add Column" tab and click on "Custom Column."

  4. In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").

  5. Use the following formula to calculate the working days:

     

    M
    WorkingDays = List.Difference( List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0)), List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0), #duration(1, 0, 0, -1)), (a, b) => Date.ToText(a) = Date.ToText(b) ), WorkingDaysCount = List.Count(WorkingDays)

     

     

    Please accept as solution if it helps 

Anonymous