Multi-criteria Summation based on First Working Day in Month

Question

Hi All,

I have rather “tricky” problem that I hope you guys can help with. I want to calculate a weekly chargeable bookings from a timesheet data that has total week hours based on different booking types.

At the start of the first working day in each month, I want to know how many chargeable bookings were worked in the previous week up on till the last Friday. E.g., week starting 01/11/2021, it will show chargeable bookings from 25 – 29/10/2021. This is a relatively straight forward calculations when the first working day of a month is on a Monday.

When the first working day in a new month is not on a Monday e.g., In week starting 05/07/2021, first working day falls on Thursday of the previous week (week starting 28/06/2021). We must account for the fact that only 2 days in the new month as elapsed and not 5 days.

Accounting for when first working day in new Month is not Monday

Date: w/c 05/07/2021

e.g., if total chargeable hours worked on w/c 28/06/2021 = 24 hrs

assumption of hours per day worked during week = 24 hrs / 5 days = 4.8 hrs per day

2 days elapsed in Month = Thurs (01/07/2021) and Fri (02/07/2021)

Chargeable hours worked as of Monday 05/07/2021 = 4.8 hrs per day * 2 days elapsed in month = 9.6 hrs.

Ignoring the start of new Month will give instead

e.g.  total chargeable hours worked on w/c 28/06/2021 = 24 hrs

assumption of hours per day worked during week = 24 hrs / 5 days = 4.8 hrs per day

5 days elapsed last week

chargeable hours worked as of Monday 05/07/2021 = 4.8 hrs per day * 5 days elapsed in month = 24 hrs.

The excel sheet attached has an example of input and final output would look like, an Alteryx input sheet and calendar mapping table that has actual elapsed days in a new month when the first working day of the month is not on a Monday

I would appreciate if somebody can help with a workflow that gets me from the input to a desired output.

tricky problem, open to ideas and solutions.  Thanks EJ

Timesheet Hours Calculation.xlsx

danilang · Answer

Hi @EJ_Alt

The fundamental problem is that the concepts of "current month" and "previous week" can't be combined in a single row.  You can have 2 previous weeks totals.  in week 07-05, you have 9.6 in the previous week that is worked in July and 14.4 hours that are worked in June.  Please provide a sample of what the correct output should look like for that week

Thanks

Dan

EJ_Alt · Answer

Hi @danilang

Thanks for your reply.

Technically, the remaining 14.4 hrs should be assigned to June. So when I look at the all hours worked in June, the last week will only be 14.4 hrs, rather than 24 hrs. I suppose the solution needs to account for hrs in the last week

Regards

EJ

danilang · Answer

Hi @EJ_Alt

It's a fairly simple matter to split the hours between this month and next.

The more complex question is, "What happens to the extra hours".   When you assign 9.6 Hrs from week 06-28 to week 07-05, how do you assign the remaining 14.4 hours worked in week 06-28?  They were worked in June and so should be assigned in June, but your current assignment method uses the values from the previous week.

Dan

ChargeableHrs.yxmd