Working days

Hi!

Here is my problem :

Our year-end date is March 31 and the last pay of the year always overlaps the first pay of the following year. I need to have the number of working days between the StartDate and March 31 of the year. I also need to ignore Holidays. I have a Holiday table in Power Query that comes from my Calendar table.



Thank you!

So this was a bit tricky, but I knocked up something that I think will do the trick. I assume you're paying bi-weekly, so knocked up a table of Pay_Start/Pay_End dates through 2031 for testing. Given that:

• I loaded this table as a Connection Only query called "PayCutoffs"
• I created a "Holiday" table of the Good Friday stats that might impact it and loaded this as Connection Only

The next thing I did was build a dynamic calendar for just vacation days that spanned from the very first day in the Pay_StartDate column to the last date in the Pay_EndDate column. (I had to do this since I didn't have a calendar table, you may be able to retrofit this to use your calendar.) The trick was that I needed to do the following:

• Add a column for Weekdays
• Merge in the Holiday table
• Filter the Weekdays column to exclude values of 0 (Sunday) and 6 (Saturday)
• Filter the Holiday column to only include null values (removing all holidays)

I called that query "WorkDayCalendar" and loaded it as a connection only as well, just to show the logic.

At this point I did the following to create the "fxWorkDaysPriorYear" function:

• Created a reference to the WorkDay Calendar
• Filtered the Dates column with a custom filter:
  • After or equal to 3/20/2014 and Before or equal to 3/31/2014
• Grouped the rows
  • I removed the grouping columns
  • I changed the column name to "WorkDays_PriorYear"

At this point I was left with a value of 8 days for 2014.

As this looked good, I then converted the new query into a function by editing the M code, making the following changes:

Summary of the changes are that I added the line at the top to turn it into a function, copied the Filtered Rows line and subbed in the variable to dynamically drive the query. I then changed the name to "fxWorkDaysPriorYear".

Finally, I used the function to generate the results against the original query:

• Created a new query referencing the PayCutoffs query
• Added a custom column with the following formula: =fxWorkDaysPriorYear([Pay_StartDate])
• Expanded that column

I've attached a sample workbook with the work done. The final results are in the WorkingDays query.

Also, not sure if you have my book, but Chapter 21 discusses creating custom functions (although you'll get a deeper understanding by reading Chapters 20-22.)

Wow thanks! It works perfectly.

I do have your book but I still need to find time to read it :S

Other approach....

Regards

Nice work, Bill. (As usual!)

Thanks for your kind words, Ken.
I just wanted to show that in this case (or similar) we do not need a calendar table.

Regards

Thanks!

Bill Szysz said:

I just wanted to show that in this case (or similar) we do not need a calendar table.

Click to expand...

True enough. Although, to be honest, if I'm loading the data to Power Pivot, I ALWAYS have a calendar table anyway. But I do get your point.

Ken, I find your version easier to understand for the basic user.

Hi again!

Bill, I am trying to use the file you shared here to calculate the working days/hours/minutes between two dates.

Here are my problems :

The transformation of the hours in a days/hours/minutes format (need to work if I have 400 days)
Exclude the lunch break
Include the Worday table because some employees work 3 or 4 days a week

EDIT : would also be really appreciated if you could help me with a formula that add X days X hours X minutes to the Start date so I could get the End date automatically in some particular situations where I have the number of working days and the start date.

Thank you very much!