Finding the Last Working Day

[junoon]

HI ALL,

I have a Sheet which has a Header Row which consists of Dates like
this:

01-May-06 02-May-06 03-May-06 04-May-06 05-May-06 06-May-06 07-May-06 08-May-06 09-May-06 10-May-06 11-May-06 12-May-06 13-May-06....etc..

on the Second row, i.e below Header row, i have Attendance marked for
employees corresponding to each day....like this:

PH L P P P WO WO LWP P P L L WO A A WO WO A etc....

On this row, in the last column, i want to create a formula which would
evaluate if there are 3 consecutive Absentisms (A) one after the
other, so that an AOD (Absent on Duty) can be raised for that employee.
i.e..

A A A

PH = Public Holiday. (rostered leave)
L = Leave (rostered leave)
P = Present
WO = Weekly Off
LWP = Leave without Pay (informed but not sanctioned leave - was
Rostered for that day.)
UL = Unpaid Leave (informed but not sanctioned leave - was Rostered for
that day.)
A - Absent

1] I want a formula such that it can evaluate, if there was a WO in
between the 3 A's.
3] find 3 consecutive A's in a row. (one after the other).
2] The Last Working Day (LWD) before the 3 A's, which could be (P, UL,
LWP) except (WO, PH, L).

PLEASE HELP ASAP

 

[junoon]

Please note that there should be 3 consecutive A's, one after the
other, & the Last Working Day would be a date (in Header column above),
which would be before the 3 A's. The only criteria is it should not be
a Weekly off (WO), or Leave(L), or PH (Public Holiday). Rest the Last
Working day could be a UL or a LWP.

So, How to find 3 consecutive A's in a Row & a LWD date (by Lookup)
before them.


PLEASE HELP ASAP.

 

[junoon]

Since its possible that there could be 3 A's in the beginning of the
month for some employees who were absent for 3 consecutive days but
later on rejoined the company ( were accepted by the company), i want
to find the latest 3 A's i.e..... 3 consecutive A's later in the month
& get a LWD just before them.


PLEASE HELP ASAP

 

[paul]

so we only have to count three a's(to begin with),or do you need to know if
there were more than three?

 

[junoon]

Hi Paul,

Thanks for your reply,

Yes, Atleast 3 A's is a must & they should be the latest ones. Then
before the 3 A's you get the Last Working Day.


Emp Name 13/5/2006 14/5/2006 15/5/2006 16/5/2006 17/5/2006
LWD
john P WO A
A A 13/5/2006
Jacob P P A
A A 14/5/2006

In the 1st case for John, his LWD would be 13/5/2006, Jacob's would be
14th.

As you can see, I want to get the LWD Dates in Column "LWD".

Hope that Helps!

 

[Domenic]

Assuming that B1:AF1 contains the data, and B2:AF2 contains the
attendance, try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

AG2, copied down:

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-2))-1,,3
),"A")=3))

AH2, copied down:

=INDEX($B$1:$AF$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:AF2,{"LWP","P","UL"},0))
,IF(COLUMN(B2:AF2)-COLUMN(B2)+1<AG2,1))))

Hope this helps!

 

[junoon]

Excellent Domenic,

Thats just pure GENIUS!

But Please explain to me the formula, i.e. how it works, so that i can
apply that to different situations i.e. modify it.

Especially the part where you divide by 1 (in both formulas).


Warm Regards,

Junoon

 

[junoon]

Sorry,

I meant 1 getting divided by the formulas.....

Could please kindly explain me the breakup of both your formulas, so
that i can understand better.

PLEASE REPLY ASAP...as i have been trying different options before &
would like to know why these formulas work properly....

 

[Domenic]

For simplicity sake, let's assume that A1:F2 contains the following
data...

Emp Name 13/5/2006 14/5/2006 15/5/2006 16/5/2006 17/5/2006
John P WO A A A

If we look at the following formula...

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:F2)-2))-1,,3)
,"A")=3))

....OFFSET references an array of ranges. Each range is made up of three
cells, B22, C2:E2, and D2:F2. And COUNTIF counts the number of A's
for each range. So, the formula is evaluated as follows...

=MATCH(2,1/({1;2;3}=3))

=MATCH(2,1/{FALSE;FALSE;TRUE})

=MATCH(2,{#DIV/0!;#DIV0!;1})

....and returns 3. Note the following:

1) The numerical equivalent of TRUE and FALSE is 1 and 0, respectively.

2) 1 divided by TRUE or FALSE will always return 1 or #DIV/0!,
respectively.

3) 2 is used as the lookup value, and will always be larger than any
value in the lookup range.

4) The range type for match is omitted, and defaults to 1.

5) Since MATCH is not looking for an exact match, and 2 is larger than
any value in the lookup range, it returns the position of the last
numerical value, ignoring #DIV/0!.

Now, the following formula...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:F2,{"LWP","P","UL"},0)),I
F(COLUMN(B2:F2)-COLUMN(B2)+1<G2,1))))

....is evaluated as follows...

=INDEX($B$1:$F$1,MATCH(2,1/IF(ISNUMBER({2,#N/A,#N/A,#N/A,#N/A}),IF({2,3,4
,5,6}-2+1<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({1,2,3,4,
5}<3,1))))

=INDEX($B$1:$F$1,MATCH(2,1/IF({TRUE,FALSE,FALSE,FALSE,FALSE},IF({TRUE,TRU
E,FALSE,FALSE,FALSE},1))))

=INDEX($B$1:$F$1,MATCH(2,1/{1,FALSE,FALSE,FALSE,FALSE}))

=INDEX($B$1:$F$1,MATCH(2,{1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}))

=INDEX($B$1:$F$1,1)

....and returns the first value indexed in B1:F1, which is 13/5/2006.

Hope this helps!

 

[paul]

Dom that s good!I was working on the same basic theory in that i had countif
and offset formulas looking at three cell ranges in a helper row to find the
LWD positions
I might have got to your formulas in a few days!

 

[junoon]

Thats Just Superb, but as Paul said, it would rather have taken me
months to figure that out!

Boss, you are the next MVP!

BTW, instead of taking "1/IF(ISNUMBER(.....i.e. dividing 1 by the array
formula values, is it not simpler to take unary characters "--", to
convert logical values to their numeric forms.

like say,..
=INDEX($B$1:$F$1,MATCH(2,--(IF(ISNUMBER(MATCH(B2:F2,{"LWP","P","UL"},0)),I
F(COLUMN(B2:F2)-COLUMN(B2)+1<G2,1)))))

PLEASE ADVICE!

Warm Regards,

Junoon

 

[Domenic]

BTW, instead of taking "1/IF(ISNUMBER(.....i.e. dividing 1 by the array
formula values, is it not simpler to take unary characters "--", to
convert logical values to their numeric forms.

As you know, IF(ISNUMBER(...),...) returns an array of 1's and FALSE
values. If you use the double negative, FALSE values will be coerced
into 0's, instead of #DIV/0!'s.

Since MATCH is constructed to return the position of the last numerical
value, unlike #DIV/0! values, 0's won't be ignored. Consequently, MATCH
won't return the desired result.

Hope this helps!

 

[junoon]

Hi Domenic,

Just to bring to your notice which i came across today in my Dept.,

Mon Tue Wed Thu Fri Sat Sun Mon 'LWD
would be Wed a "Present" here...
P P P A A WO WO A

Or,

Mon Tue Wed Thu Fri Sat Sun Mon 'LWD
would be Thu a "Leave" here...
P P P L A A WO A

I found that, (in the above examples) all employees are entitled to
their "WO" & i will need to wait for the next day of absence (I.e
"Mon") to have the AOD (Absent On Duty) initiated.

1] So, from above examples, LWD would be a Leave(L), Present, LWP or
UL. i.e a sanctioned Leave would also be considered a Last Working day.

2] And a WO can lie between the 3 A's also.

In this scenario, What modifications should be made to the 2 formula's?

Warm Regards,

Junoon

 

[Domenic]

A few questions...

1) In your first example, you have two WO's within the three A's. Is
this correct? If so, how many WO's can you have within the three A's
and still initiate an 'Absent On Duty'?

2) In your second example, the following initiates an AOD...

A A WO A

....I assume the same is true for the following...

A WO A A

....right?

 

[junoon]

Yes, that true Domenic.

Generally, in a week every one has 2 weekly offs, so an A A WO
WO A...
can also initiate an AOD.

But sometimes, the weekly offs may not lie one after the other, as
shown in the previous example. e.g. A A WO A....

Warm Regards,

Junoon

 

[Domenic]

See if the following returns the desired result. You'll notice that it
uses 4 helper cells. These cells can be hidden, if you want.

So, assuming that B1:AF1 contains the date, and B2:AF2 contains the
attendance, try...

AG2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-2))-1,,3
),"A")=3))

AH2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/((COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-3))-1,,
4),"A")=3)*(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-3))-1,,4
),"WO")=1)))

AI2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/((COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-4))-1,,
5),"A")=3)*(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-4))-1,,5
),"WO")=2)))

AJ2, copied down:

=MAX(IF(ISNUMBER(AG2),AG2),IF(ISNUMBER(AH2),AH2),IF(ISNUMBER(AI2),AI2))

AK2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$1:$AF$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:AF2,{"L","LWP","P","UL"}
,0)),IF(COLUMN(B2:AF2)-COLUMN(B2)+1<AJ2,1))))

Hope this helps!

 

[junoon]

Thanks PAL!

See if the following returns the desired result. You'll notice that it
uses 4 helper cells. These cells can be hidden, if you want.

So, assuming that B1:AF1 contains the date, and B2:AF2 contains the
attendance, try...

AG2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-2))-1,,3
),"A")=3))

AH2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/((COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-3))-1,,
4),"A")=3)*(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-3))-1,,4
),"WO")=1)))

AI2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(2,1/((COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-4))-1,,
5),"A")=3)*(COUNTIF(OFFSET(B2,,ROW(INDIRECT("1:"&COLUMNS(B2:AF2)-4))-1,,5
),"WO")=2)))

AJ2, copied down:

=MAX(IF(ISNUMBER(AG2),AG2),IF(ISNUMBER(AH2),AH2),IF(ISNUMBER(AI2),AI2))

AK2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$1:$AF$1,MATCH(2,1/IF(ISNUMBER(MATCH(B2:AF2,{"L","LWP","P","UL"}
,0)),IF(COLUMN(B2:AF2)-COLUMN(B2)+1<AJ2,1))))

Hope this helps!