Forum Discussion
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 with this.
Thank you.
Anonymous , Try using this method
Open the Power Query Editor.
Select the table that contains your start and end date columns.
Go to the "Add Column" tab and click on "Custom Column."
In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").
Use the following formula to calculate the working days:
MWorkingDays = 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
2 Replies
Anonymous , Try using this method
Open the Power Query Editor.
Select the table that contains your start and end date columns.
Go to the "Add Column" tab and click on "Custom Column."
In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").
Use the following formula to calculate the working days:
MWorkingDays = 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
, Try using this method
Open the Power Query Editor.
Select the table that contains your start and end date columns.
Go to the "Add Column" tab and click on "Custom Column."
In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").
Use the following formula to calculate the working days:
MWorkingDays = 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