Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
so we only have to count three a's(to begin with),or do you need to know if
there were more than three? -- paul remove nospam for email addy! "junoon" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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:A F2)-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! In article .com, "junoon" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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.... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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:F 2)-2))-1,,3) ,"A")=3)) ....OFFSET references an array of ranges. Each range is made up of three cells, B2:D2, 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! In article .com, "junoon" wrote: 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.... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
In article . com,
"junoon" wrote: 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! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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? In article . com, "junoon" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
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:A F2)-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:"&C OLUMNS(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:"&C OLUMNS(B2:AF2)-4))-1,,5 ),"WO")=2))) AJ2, copied down: =MAX(IF(ISNUMBER(AG2),AG2),IF(ISNUMBER(AH2),AH2),I F(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! In article . com, "junoon" wrote: 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Last Working Day
Thanks PAL!
Domenic wrote: 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:A F2)-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:"&C OLUMNS(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:"&C OLUMNS(B2:AF2)-4))-1,,5 ),"WO")=2))) AJ2, copied down: =MAX(IF(ISNUMBER(AG2),AG2),IF(ISNUMBER(AH2),AH2),I F(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! In article . com, "junoon" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
click & drag not working | Excel Discussion (Misc queries) | |||
amount of working days per month | Excel Discussion (Misc queries) | |||
Calculation with Working day of the year | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Working time and days | Excel Worksheet Functions |