Allocation Status Based on Date

[david gabra]

Hi

I have 2 tabs in the excel.

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

#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)

thank you

David.

Sample file added of course.

 

[vletm]

david gabra
Is there clear reason why 'Master'-sheet has none dates?
Is there clear sample results, how those 'Missions' would solve?

 

[david gabra]

Hi

Is there clear reason why 'Master'-sheet has none dates? - Updated now.

Is there clear sample results, how those 'Missions' would solve? Yes
If you refer to the 'Master Tab' - column D - that is how it should look.

also, really complicated for you - - but not a ninja - I hope.

If the payroll (master sheet) starts on the early month, for example of the Nov 8th and the person started later (Local - Expat sheet) in our example of on the Nov 11th - so I need instead on Null to state "include 19 days) - i.e. to take the total number of the days in the month (nov has 30 and minus the number of days already employed (11 days). This is super critical for me providing exact data and I totally out of my capabilities at present.



thank you

David.

 

[david gabra]

Hi

Is there clear reason why 'Master'-sheet has none dates? - Updated now.

Is there clear sample results, how those 'Missions' would solve? Yes
If you refer to the 'Master Tab' - column D - that is how it should look.

also, really complicated for you - - but not a ninja - I hope.


Please see logic neededed.

If the person (Local - Expat sheet) in our example starts on the Nov 11th - so I need to state "include 19 days - i.e. to take the total number of the days in the month (nov has 30) and minus the number of days already employed (11 days). This is super critical for me providing exact data and I totally out of my capabilities at present.



Also I just thought of another complication, if the employee turned from being a local to expat, or an expat to a local (eg. in February) - I need to know how may day the employee was an expat and how may days the person was local.

thank you ninja





thank you




October 8, 2017EURNo Status0
November 8, 2017EURExpat -19 days0
December 9, 2017EURExpat0
January 9, 2018EURExpat0
February 9, 2018EURExpat - 3 days - local 25 days0
March 12, 2018EURLocal0




I have added a new updated tab file with different payroll date, copying with the join what you taught me.

I hope that is correct

 

[vletm]

david gabra
If Your opinion is that something would be complicated,
then You should explain what, why, how to make it clear.
If something is super critical for someone,
then everything should be super clear too.
Something seems to be even complicated for You,
if You'll need more and more every time.
It would make double or more coding, if need to add add more features.
There are still terms which aren't in both; file and Your 'needs'.
You finished Your text: I hope that is correct.
... That means ... You are not sure at all!
... and there would come more changes or something.

 

[david gabra]

I really tried to make it as clear as possible. I guess I did not succeed on this point. I will close this post if you wish.
I apologize for any misunderstanding.
I will try and manage somehow.