Timeline and hours to months of the year

[mojomathers]

I have an extract from Monday.com that I would like some help with. We would like to use the xlsx output (example below) to monitor how busy people are on a monthly basis. In order to do this, the Hours should be divided across the months in the Delivery Timeline - Start and Delivery Timeline - End (see second example).

Can anyone help with how to do this?

Client	Employee	Grade	Delivery Timeline - Start	Delivery Timeline - End	Hours
ABC	Jim	Sales rep	2023-06-14	2023-08-14	30
ABC	Pam	Receptionist	2023-09-06	2023-10-06	20
ABC	Dwight	Assistant to the Regional Manager	2023-08-14	2023-08-14	30
DEF	Michael	Regional Manager	2023-08-29	2023-08-29	50
DEF	Kevin	Accounts	2023-08-01	2023-09-01	10
DEF	Andy	Sales rep	2023-08-24	2023-09-25	10

Desired output:

Client	Employee	Grade	Jun	Jul	Aug	Sep	Oct
ABC	Jim	Sales rep	10	10	10
ABC	Pam	Receptionist				10	10
ABC	Dwight	Assistant to the Regional Manager			30
DEF	Michael	Regional Manager			50
DEF	Kevin	Accounts			5	5
DEF	Andy	Sales rep			5	5

[caltang · Accepted solution]

I've made it dynamic for you to account for Jan till Dec.

Hope this sparks some ideas!

[mojomathers]

Thanks, this has solved the original question! If I wanted to fine tune it, so the allocation of hours is accurate depending on the number of days in each month the timeline covers how would I do that?

Example: 

Client	Employee	Grade	Delivery Timeline - Start	Delivery Timeline - End	Hours
ABC	Jim	Sales rep	2023-06-14	2023-08-14	30

Desired Output:

Client	Employee	Grade	Jun	Jul	Aug	Sep	Oct
ABC	Jim	Sales rep	7	16	7

And not:

Client	Employee	Grade	Jun	Jul	Aug	Sep	Oct
ABC	Jim	Sales rep	10	10	10

[caltang]

In order to do that, your original data must indicate the hours worked per month! 

Any 3 number slots can combine to 30. 

10 10 10

10 8 12

8 8 14

etc.

Either that or you have a separate worksheet that tells that data.

Do you have it on hand? @mojomathers 

[mojomathers]

Unfortunately the data is not defined like that, so it would be a case of calculating the percentage of days to allocate to each month (or something like that).

[caltang]

So % of the month worked right? For example, Jim started mid-month. So whatever business days left, will be the % of the count, no?

You will need to count only business days right? What about business hours? Do they impact or no? Do Holidays / Weekends count? Does Overtime count? 

You will need to list out your requirements. If you do, then yes, it is possible to account for them.

[mojomathers]

You raise very good questions. It would need to be allocated as below:

• Only business days (Mon - Fri)
• 7.5 hours per day
• Holidays / Weekends / Overtime does not count

[caltang]

Can you provide another random set of data more closely linked to your real data? 

Then my new workflow to you would account for your new requirements to the best of my ability.

[mojomathers]

Sure, the below is actual data where I have just removed client and employee names. There will be some blanks in the data as well:

Engagement	Role	Delivery Timeline - Start	Delivery Timeline - End	Employee	Hours
Client A	Partner	14/06/2023	14/06/2023		1
Client A	Engagement Manager	06/09/2023	06/09/2023		1
Client A	Team Manager	14/08/2023	14/09/2023	Martin	20
Client A	Team Manager	29/08/2023	29/08/2023	Martin	7
Client A	Team Manager	01/08/2023	01/08/2023	Martin	3
Client A	Team Member	24/07/2023	25/08/2023		10
Client A	Team member	29/08/2023	30/09/2023		14
Client A	Team member	04/09/2023	05/09/2023		3
Client A	Specialist				0
Client B	Partner
Client B	Engagement Manager
Client B	Team Manager
Client B	Team Member	31/07/2023	03/08/2023	René	7
Client B	Team member	31/07/2023	03/08/2023	René	7
Client B	Specialist

[caltang · Accepted solution]

Specific Hours tabulated:

Kindly mark this as another accepted solution so that others may benefit as well.