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

# Expression Issues

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

• ###### Re: Expression Issues

hei

attach is an example

it uses the interval match function

hope it helps you

• ###### Re: Expression Issues

Thanks liron for ur reply. This solution helps me in getting the correct results.

Regards,

Saurabh Mudgil