Calculating Standard hours for Each Employee Dynamically

[DataPirate26]

Hi All,

Below I have shared a dataset which has Data for the Month of July and August 2022. I have to calculate the Standard Business Hours for Each Employee Month wise Dynamically (Location Wise also). 

It should be calculated based on the Hire date of every individual and Last Day of Work. So the calculation should be like this – say, we are creating the report for July 2022 and we have 20 business days in the month. So the standard hours should be 20*8 for all the resources (Location Wise) except those who joined on different dates of July or left the firm on different dates in July. For an example if one individual joined on 9th July and had 17 business days, then the standard hours will be 17*8 for that resource. Similarly if one individual has left the firm on 12th July and got 9 working days then the standard hours will be 9*8.

To Calculate the number of Working Days we will have to Exclude the Dates which fall in Weekends (Sat, Sunday) Also Exclude the Firm Holidays for which I have shared a Holiday List File below. 

Note: "Date" is the field which will tell us it has Data for which months. I have just shared data for 2 months but it will have data for Multiple months. 

[DataPirate26]

@binuacs @Qiu Can you help me out on this ? 

[Raj]

Hi,

Some questions before solving this

1. last day of working is completely null in the dataset

2. what does the Day refer here please elaborate

3. why is the sum of hours 16 or 12 does this mean he/she has worked for this many hours on that day?

4. Can I drop task Name?

[DataPirate26]

@Raj Thanx for responding. I am clarifying all the questions below: 

1 ) So Last day of Work is Blank means that Person is Active in the Organisation. You will also Find Employees who will have Status as Terminated and their Last working Day is for Example : 29th Sept 2022. They are also Active as this Dataset I have shared is of Month July and August and You can understand that from the Field "Date". 

2 ) Day is basically the Weekdays I have taken as I have to Exclude the Weekends and the Firm holidays from the "Date" field. Firm  holidays you will get in the holiday List File. 

3) Ignore that and check the updated Excel Input File and the holiday List file I have shared below. 

4) Yes you can drop the task name

Note: Basically I need to calculate the standard hours of each Individual based on the Hire Date and the Last working Date Dynamically. To calculate the number of Business Days we will have to exclude the Weekends and the Firm Holidays ( Provided in the Holiday List file) from the Date Field provided in the main dataset.  

Let me know if you have any more doubts I will clarify It. 

[DataPirate26]

@Raj Sorry, Forgot to attach the updated files. Given Below. 

[DataPirate26]

@Raj @binuacs Anything on this ? 

[Raj]

solved for active ones use the same logic for inactive ones 

Hope this helps

do let me know in case of any other help

[DataPirate26]

@Raj no no this is not what I was talking about. You will see there will be Hire Date and Last day of work for every individual. The Date Field gives each of the dates in which they have clocked Hours. Standard hours each Day one person Clock is 8. 

So I have to figure out the number of Working days for the month which would be Number of Days Each individual has clocked hours but we will have to exclude the dates which falls in weekends and the Firm Holidays.

Now Suppose XYZ candidate joins the firm on 10th of July. In that case the number of working days for him would be less as compared to the other employees who has clocked hours in July. For that employee it would be maybe 10. So standard hours for him would be 10*8 = 80 whereas for other employees it would be 20*8 = 168. Similarly we have to check who has left the firm on that particular month and accordingly we have to calculate their standard working hours. 

[Raj]

Give me some time 

busy with some meetings regarding Alteryx presales  

will get back soon

[Christina_H]

Is this what you need?  I used the working days formula from here: Solved: Count workday difference between 2 dates - Alteryx Community