Daily Work Load report, Date range as Input

I need to calculate the daily work load of individuals in Sheet1 another sheet2, I don't have enough knowledge when it comes to VBA and macros so I encounter a lot of problem.

First is that the input Date is range only from "start date" to "end date" but I need to create a daily report of the individuals work load.

second some individuals has two running task that is within one of the date range and it has different equivalent Work load values. some work are done by pair or alone.

also will be a big help if you cant point me to the right direction for me to accomplish this. Big thanks anyone who'll help..

How is the workload apportioned when there are two people assigned?

I presume the year values in row 2 are incorrect, and should be 2019?

Assuming there's no split of workload when there are two people per row, you can use this Power Query. Format your source data as a table, then use:

Please Login or Register  to view this content.

To divide the workload between 2 people, where needed, amend this line:

Please Login or Register  to view this content.

To update the output after additional data is added to the source table, simply use Data > Connections > Refresh All

Originally Posted by jamnaz0726

is person 1, person 2 created then combined?

Exactly right. You could do this with multiple manual steps, in which case those three lines of code would be replaced with:

Please Login or Register  to view this content.

I just manually combined those steps, for brevity, in the initial query.

Did this solve your issue?

Yes. Add another query, named fnNetWorkdays:

Please Login or Register  to view this content.

Now change this line in the original query:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

this line of code always seems to mess my data selections?

i have 15 column sheet originally after i have removed un-needed columns and rows my final sorted sheet is 5 columns, But then when i add the code, my sheet turns back to the original 15 column sheet?

This query works exactly as expected with the example data provided.

I don't recognise a 15 column sheet - what are the additional columns?

Upload a workbook which shows the actual structure of your data.

I just spotted that we need to tweak the #"Added Date List" step, to exclude weekends from the calculations, too:

Please Login or Register  to view this content.

Using that file:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.