2 Replies Latest reply: Sep 29, 2011 5:01 AM by Saurabh Mudgil RSS

    Expression Issues

    Saurabh Mudgil

      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:

       

      StartDateEndDateEmpNoEmpNameLocation
      Position
      1-Aug-201115-Sep-201111001SSSSDelhiAssistant Manager
      16-Sep-2011To present11001SSSSMumbaiManager

       

      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