Calculating Standard hours for Each Employee Dynamically

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.

Input Data.yxdb

Holiday List File.xlsx

Dynamic Processing

Accepted answers

DataPirate26

@Christina_H Thanx for all your assistance. Your approach was correct. This was resolved.

All comments

DataPirate26

@binu_acs @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.

Test Data.xlsx

Holiday List File.xlsx

DataPirate26

@Raj @binu_acs 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

New Workflow4.yxmd

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

Working days.yxmd

DataPirate26

@Christina_H Thanx for the help but this is not exactly how I want . So basically the working days here has been calculated based on the the first date and the last date the employee has clocked the hours.

But I want in this way : For Example if My hire date is 12th of july So for that month leaving the dates Till 11th it will go for all the working dates that are there in the" Date" field , So if the number of working days for all is 21 days Then for everyone it will be 21*8 and for this employee we will count from 12th july to Last date of that month. Then multiply by 8.

Christina_H

@DataPirate26 I believe that's what I'm doing. The start and end date used to calculate the number of days are calculated to be either the start/end of the month or the hire/termination date of the person as appropriate

No worries if this isn't what you need though, hopefully someone can help you find the answer.

DataPirate26

@Christina_H Thanx for all your assistance. I checked it and i see it is partially right. But for Few Employees If the Hire Date is for example 11th July 2022 for that employee also the number of working days is showing as 22 and the standard hours is coming as 176 which shouldn't be the case. If you can re-check once and make this correct I guess then its fine.

Qiu

@DataPirate26
I am sorry I can not respond. Get so much going on work.
I am sure someone will figure this out. 😁

DataPirate26

@Qiu Not a problem at all. I am working on it. Thanx a lot.

Christina_H

@DataPirate26 I'm calculating the hours separately for July and August, is that where the confusion comes in? An employee with a hire date of 11 July would have worked the whole of August (22 days, 176 hours)

DataPirate26

@Christina_H Thanx for all your assistance. Your approach was correct. This was resolved.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335