How to update data in multiple rows based on certain rules

[lkaivan]

Hi All,

I have attached a sample file with attendance data that I am analyzing. I want to update the attendance type field in cases where an employee has taken a Leave Without Pay (LWP) or Leave of Absence before and after a weekend/weekly-off/holiday.

The rule is that if an employee's attendance is:

01/03 LWP/Absent

02/03 Holiday

03/03 WeeklyOff

04/03 WeeklyOff

05/03 LWP/Absent

then the days between the LWPs/Absent days need to be converted to Absent. Unless there is an absent or a LWP on either side of the holidays/days off, there is no sandwiching. This is the rule for "sandwiching" of leaves.

The days of weekly-offs and holidays are not fixed for all employees. Also, it is not necessary that weekly-offs will be on consecutive days.

Could someone please advise me on how I should approach this step of my analysis?

[BenMoss]

@lkaivan

For clarification, could you add a column to the file you attached which shows what the attendence type would be ammended to read based on the rules (if it changes at all which it doesn't in most cases!).

That would help us know exactly what you are looking for.

Ben

[lkaivan]

Hi Ben,

Sorry for not adding this earlier. I have added values in column D (highlighted in green). This column is what the output should look like.

You may refer the cells (in the shade of orange) in column C against what is given in column D; I guess you should be able to get more clarity on what I am asking.

Thank you!

[BenMoss · Accepted solution]

Perfect, that certainly help.

Here's the logic I have built to solve the problem. It may not be perfect but it should give a starting point.

[lkaivan]

Hi Ben,

I think it has worked. I'll take it forward from here.

Thank you!