6 Replies Latest reply on Nov 24, 2018 12:32 PM by David Gabra

    Allocation Status Based on Date

    David Gabra

      Hi

       

      I have 2 tabs in the excel.

       

      1. Local  - Expat tab - that shows the status of an employee (local / expat) based on dates.

       

        

      Employee NumberPerson TypeEmployment Start Date
      11LocalJune 1, 2016
      22ExpatNovember 11, 2017
      22LocalFebruary 3, 2018
      33ExpatDecember 2, 2013

       

      #2. Master tab: show the dates that an employee was paid by MONTH

       

      Mission: Part A)

      ---> I need to assign a status to the employee for each month that the employee was paid of  (either local or expat).

      This should be done by using the employee status from tab #1 - and

      assigned that to the payroll file:

      eg. if of the Feb 3rd an employee became a local - so from Feb inwards the employee status should be local, updated from prior status of expat.

       

      If there is a payroll month BEFORE  the status start dates on the tab # - then add - "No Status".

       

      Part B) Once part A is achieved this should be easy.

       

      I need to know whether  an employee is eligible for  Travel .

       

      Rule:

       

      If an local - not eligible   (allowed no)

      If an expat - yes eligible. (allowed yes)

       

       

      I am not sure the type of join needed and what to do afterwards.

       

       

      thank you!!!!!!!

       

      David.

       

      Sample file added of course.