How to update data in multiple rows based on certain rules

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?

Sample attendance data.xlsx

Workflow

Preparation

Iterative Macro

Accepted answers

BenMoss

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.

SampleWorkflow.yxmd

All comments

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!

Sample attendance data.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703