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]

@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 · Accepted solution]

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