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

    Allocation Status Based on Date

    David Gabra



      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 .




      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!!!!!!!




      Sample file added of course.