Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing one issue that is i am having a scenario in which i have to calculate the number of working days and standars hours. I am attaching the XL file in which there are basically 6 fields i want to calculate number of working days and then standard hours correspondingly.
Scenario is : Suppose there is an employee whose emp_number is 1101 whose Assignment(Asssdate) starts on 1-08-2011 and ends(Asseddate) on 15-09-2011. So my requirement is to calculate number of working days and then standard hours basis on this date difference excluding saturday and sunday. I am using NetWorkDays() function for that but problem is that this report is prepare at the end of each month so if:
StartDate | EndDate | EmpNo | EmpName | Location | Position |
---|---|---|---|---|---|
1-Aug-2011 | 15-Sep-2011 | 11001 | SSSS | Delhi | Assistant Manager |
16-Sep-2011 | To present | 11001 | SSSS | Mumbai | Manager |
My start date is 1 aug 2011 and end date is 15 sep 2011 in between there are near about 33 working days excluding saturday,sunday. So if i want to see the report for aug month only then it will show the working days upto 30 aug 2011. And when i want to see the report for September month then it will show the results from 1-sep-2011 to 31-sep-2011 i.e half results are coming from the previous assignment and half from the present assignment. This particular employee is presently working as a Manager in Mumbai.
How i am calculating is :
I am making one variable named as MaxAssdate = IF( ISNULL(YearType),MAX(Asssddate), DATE(NUM(30) &'/' &MAX(CalendarMonthNum) & '/' & MAX(NUM(left(CalendarNumYearMonth,4))),'DD/MM/YYYY'))
For Working Days =
NetWorkDays(
(IF(Asssddate >= DATE(MONTHSTART(MaxAssdate),'DD/MM/YYYY'),DATE(Asssddate,'DD/MM/YYYY'),DATE(MONTHSTART(MaxAssdate),'DD/MM/YYYY'))),
(IF(Asseddate <= DATE(MonthEnd(MaxAssdate),'DD/MM/YYYY'),DATE(Asseddate,'DD/MM/YYYY'),IF(MONTH(DATE(MONTHEND(MaxAssdate),'DD/MM/YYYY'))=MONTH(TODAY()),
DATE(TODAY(),'DD/MM/YYYY'),DATE(MONTHEND(MaxAssdate),'DD/MM/YYYY'))))
)
For Standard Hours:
I am making one variable sd = Count(DISTINCT [EmpployeeNo&EmployeeName])
Standard Hours :
if(
if(sd > 1, [Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)),
if(NOOFROWS()>1,([Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))/TOTDAYS,
[Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))) > 184, 0,
if(sd > 1, [Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)),
if(NOOFROWS()>1,([Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))/TOTDAYS,
[Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))))
There is a logic behind calculating standard hours i.e if SU_Jobname = 'Staff Accountant ' then take 7 hours as a working hours in a day otherwise taking 8 hours. But this logic is not working fine. Please let me know any other simple way to calculate this asp.
I am attaching the XL file for your reference.
Regards,
Saurabh Mudgil
hei
attach is an example
it uses the interval match function
hope it helps you
Thanks liron for ur reply. This solution helps me in getting the correct results.
Regards,
Saurabh Mudgil