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
