Total Working Days Excluding Weekends & Holidays using Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to calculate total working days excluding weekends and holidays using Power Query. Let’s get started

Tutorial Video

I recommend you watch the video for more in-depth explanation

Sample Data

In the caption below, we have list of dates from 01/01/2020 to 31/12/2020 in column A. In column, D and E, we have the List of Holidays and the corresponding dates respectively. Note, the holidays is specific to Nigeria.

Format Dataset As Excel Table

The next thing we are going to do is formatted the two dates in columns A and E as an Excel Table which is required to get the data into Power Query for cleaning and Transformation

  • Therefore, click inside the data in column a and press CTRL + T

  • In the Create Table dialogue box, my table has headers is automatically checked.
  • Click OK
  • Click anywhere inside the dataset in columns D and E and press CTRL + T
  • Click OK

The two data are formatted in Excel Table as seen in the caption below.

Kindly refer to the video above for a trick to get the data formatted and import to Power Query

Import Data to Power Query

Next, we need to get the data into Power Query. In this example, we need to first import the holidays data in columns D and E into Power Query

To do that:

  • Click anywhere inside the data in columns D and E
  • In the Get & Transform Data group of the Data tab, click on From Table/Range

This will open the Power Query editor as seen in the caption below

Transformation: Data Types

The only transformation we want to do is to change the data type from Data/Time to Data. To do this:

  • Click the clock and time icon to the left of the Data heading and select Date.
  • In the intermediate dialogue box, select Replace Current or Add a new step.

The data has been transformed to only show Date Data Type as seen in the caption below

  • In the Home tab, select Close & Load and Close & Load To.
  • In the Import Data dialogue box, click o n Only Create Connection as seen below and press OK

  • Click anywhere inside the data in column A which contains date
  • In the Get & Transform Data group of the Data tab, click on From Table/Range
  • Click the clock and time icon to the left of the Data heading and select Date.
  • In the intermediate dialogue box, select Replace Current or Add a new step.

The data has been transformed to only show Date Data Type as seen in the caption below

Merge Queries

Next, we want to merge the two queries

  • From Table1 query which contains all the data from 01/01/2020 to 31/12/2020, in the Combine group of the Home tab, click on Merge Queries
  • In in the Merge dialogue box, select Dates
  • Select Table2 from the drop-down and select Date
  • Click on the Join Kind drop-down and select Left Anti (rows only in first) which excludes all the dates in the holidays from the matching dates. See the caption below

From the caption below, we can see the 01/01/2020 which is New Year’s Day and which falls under the 11 holidays, has been excluded.

Remove Column

We do no need the Table2 column, therefore to remove it, right-click on it and choose Remove

Insert Day Name

Next, we want to insert the name of the day.

  • To do that, In the From Date & Time group of the Add Column tab, click on Date drop-down
  • Select Day and Name of Day 

In the caption below, we have the corresponding name of day based on the date in each rows. See the caption below

Filter Out Weekends

Next, we want to filter out Saturdays and Sundays which are weekends

  • To do that, click on the Dates filter button and un-check Saturday and Sunday. Refer to the video for another method to filter out the weekends

In the caption below, we have all the working days excluding weekends and holidays

  • Close and Load
  • Finally, click on Close & Load and select Close & Load To

In the Import Data dialogue, we want Table which is default. We want to place the data in cell G1 of the Existing worksheet which I have specified as seen in the caption below

In the caption below, the data is ready. From the Status Bar, we have 254 workings days excluding weekends and holidays

Conclusion

Power Query is ready a cool feature that we have seen how to use to calculate total workings days. Do refer to the video and subscribe to my YouTube channel for more power videos

Posted in Blog and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *