COMPLEX FORMULA NEED

AABID QURESHI

New Member
Joined
Dec 6, 2020
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
Hi this is Aabid here.
I need a formula which returns me start and end date of Leave, Absent and likely to be given values.

Suppose an employee is on leave from 08 to 17, it is marked in sheet, then it should return first start date and end date.
Please help about it.



S. No.Employee CPeEmployee NameDesignationDepartmentDate Joined01-12-202002-12-202003-12-202004-12-202005-12-202006-12-202007-12-202008-12-202009-12-202010-12-202011-12-202012-12-202013-12-202014-12-202015-12-202016-12-202017-12-202018-12-202019-12-202020-12-202021-12-202022-12-202023-12-202024-12-202025-12-202026-12-202027-12-202028-12-202029-12-202030-12-2020FROMTO
1242MAHAVEER SINGH NATHWATSR. FOREMANPLANT & EQUIPMENTS19-03-2006WPPPPODODODODODODPPHHAAPPPPWPPPPPPPP
2448ARVIND KUMARCONSTRUCATION MANAGERHIGHWAY01-08-2007WPPPPPPWPPPPELHHPPPPPPWPPPPPPPP
3787RAJESH SINGALPROJECT COORDINATOROPERATION25-04-2008WPPPPPPELELELELELELHHELELPPPPWPPPPPPPP
41489ASHOK PRASAD SINGHFOREMANHIGHWAY15-02-2010WPPPPPPWPPPPPHHPPPPPPWPPPPPPPP
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Welcome to MrExcel Message Board.
What means Abbreviations?
and Which one you need to use at formula?
 
Upvote 0
If A = Absent and Your Date columns are in column G to AJ
Then From is in column AK (AK2) and formula is : (for first A)
Excel Formula:
=IFNA(INDEX($G$2:$AJ$2,,MATCH("A",$G3:$AJ3,0)),"")

and for TO column (you should Press Ctrl+Shift+Enter for this):
Excel Formula:
=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("A"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G3)+1),2)),"")

and which abbreviation means leave????
 
Upvote 0
If A = Absent and Your Date columns are in column G to AJ
Then From is in column AK (AK2) and formula is : (for first A)
Excel Formula:
=IFNA(INDEX($G$2:$AJ$2,,MATCH("A",$G3:$AJ3,0)),"")

and for TO column (you should Press Ctrl+Shift+Enter for this):
Excel Formula:
=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("A"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G3)+1),2)),"")

and which abbreviation means leave????
sir,
S. No.Employee CODEEmployee NameDesignationDepartmentDate Joined01-12-202002-12-202003-12-202004-12-202005-12-202006-12-202007-12-202008-12-202009-12-202010-12-202011-12-202012-12-202013-12-202014-12-202015-12-202016-12-202017-12-202018-12-202019-12-202020-12-202021-12-202022-12-202023-12-202024-12-202025-12-202026-12-202027-12-202028-12-202029-12-202030-12-2020LEAVE FROMTOABSENT FROMTO
1242MAHAVEER SINGH NATHWATSR. FOREMANPLANT & EQUIPMENTS19-03-2006WPPPPODODODODODODPPHHAAPPPPWPPPPPPPP
2448ARVIND KUMARCONSTRUCATION MANAGERHIGHWAY01-08-2007WPPPPPPWPPPPELHHPPPPPPWPPPPPPPP
3787RAJESH SINGALPROJECT COORDINATOROPERATION25-04-2008WPPPPPPLLLLLLHHLLPPPPWPPPPPPPP
41489ASHOK PRASAD SINGHFOREMANHIGHWAY15-02-2010WPPPPPPWPPPPPHHPPPPPPWPPPPPPPP

This is the sheet,
Where "L" indicates "Leave"
"A" Indicates "Absent"

i want to use a formula in
If A = Absent and Your Date columns are in column G to AJ
Then From is in column AK (AK2) and formula is : (for first A)
Excel Formula:
=IFNA(INDEX($G$2:$AJ$2,,MATCH("A",$G3:$AJ3,0)),"")

and for TO column (you should Press Ctrl+Shift+Enter for this):
Excel Formula:
=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("A"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G3)+1),2)),"")

and which abbreviation means leave????
the given formula is not working
 
Upvote 0
You should change range address to your range address.
or Take Screenshot from Pages and Upload to I correct formula.
And this is for Start and End Date of Leaves
Excel Formula:
=IFNA(INDEX($G$2:$AJ$2,,MATCH("L",$G3:$AJ3,0)),"")

You should Press Ctrl+Shift+Enter for this:
Excel Formula:
=INDEX($G$2:$AJ$2,SMALL(IF("L"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G$3)+1),COUNTIF(G3:AJ3,"L")))

and You can Count All of Leaves also if want
 
Upvote 0
You should change range address to your range address.
or Take Screenshot from Pages and Upload to I correct formula.
And this is for Start and End Date of Leaves
Excel Formula:
=IFNA(INDEX($G$2:$AJ$2,,MATCH("L",$G3:$AJ3,0)),"")

You should Press Ctrl+Shift+Enter for this:
Excel Formula:
=INDEX($G$2:$AJ$2,SMALL(IF("L"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G$3)+1),COUNTIF(G3:AJ3,"L")))

and You can Count All of Leaves also if want
can you share me this sheet with formula,
i am trying it but not getting success.

aqureshi903@gmail.com
 
Upvote 0
This is (I used "EL" in formula not "L"):
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1
2 No.Employee CPeEmployee NameDesignationDepartmentDate Joined1/12/20202/12/20203/12/20204/12/20205/12/20206/12/20207/12/20208/12/20209/12/202010/12/202011/12/202012/12/202013-12-202014-12-202015-12-202016-12-202017-12-202018-12-202019-12-202020-12-202021-12-202022-12-202023-12-202024-12-202025-12-202026-12-202027-12-202028-12-202029-12-202030-12-2020Leave FROMTOAbsent FROMTO
31242MAHAVEER SINGH NATHWATSR. FOREMANPLANT & EQUIPMENTS19-03-2006WPPPPODODODODODODPPHHAAPPPPWPPPPPPPP  16-12-202017-12-2020
42448ARVIND KUMARCONSTRUCATION MANAGERHIGHWAY1/8/2007WPPPPPPWPPPPELHHPPPPPPWPPPPPPPP13-12-202013-12-2020
53787RAJESH SINGALPROJECT COORDINATOROPERATION25-04-2008WPPPPPPELELELELELELHHELELPPPPWPPPPPPPP8/12/202017-12-2020
641489ASHOK PRASAD SINGHFOREMANHIGHWAY15-02-2010WPPPPPPWPPPPPHHPPPPPPWPPPPPPPP  
7
8
Sheet2
Cell Formulas
RangeFormula
AK3:AK6AK3=IFNA(INDEX($G$2:$AJ$2,,MATCH("EL",$G3:$AJ3,0)),"")
AL3:AL6AL3=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("EL"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G$3)+1),COUNTIF(G3:AJ3,"EL"))),"")
AM3AM3=IFNA(INDEX($G$2:$AJ$2,,MATCH("A",$G3:$AJ3,0)),"")
AN3AN3=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("A"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G3)+1),2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
This is (I used "EL" in formula not "L"):
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1
2 No.Employee CPeEmployee NameDesignationDepartmentDate Joined1/12/20202/12/20203/12/20204/12/20205/12/20206/12/20207/12/20208/12/20209/12/202010/12/202011/12/202012/12/202013-12-202014-12-202015-12-202016-12-202017-12-202018-12-202019-12-202020-12-202021-12-202022-12-202023-12-202024-12-202025-12-202026-12-202027-12-202028-12-202029-12-202030-12-2020Leave FROMTOAbsent FROMTO
31242MAHAVEER SINGH NATHWATSR. FOREMANPLANT & EQUIPMENTS19-03-2006WPPPPODODODODODODPPHHAAPPPPWPPPPPPPP  16-12-202017-12-2020
42448ARVIND KUMARCONSTRUCATION MANAGERHIGHWAY1/8/2007WPPPPPPWPPPPELHHPPPPPPWPPPPPPPP13-12-202013-12-2020
53787RAJESH SINGALPROJECT COORDINATOROPERATION25-04-2008WPPPPPPELELELELELELHHELELPPPPWPPPPPPPP8/12/202017-12-2020
641489ASHOK PRASAD SINGHFOREMANHIGHWAY15-02-2010WPPPPPPWPPPPPHHPPPPPPWPPPPPPPP  
7
8
Sheet2
Cell Formulas
RangeFormula
AK3:AK6AK3=IFNA(INDEX($G$2:$AJ$2,,MATCH("EL",$G3:$AJ3,0)),"")
AL3:AL6AL3=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("EL"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G$3)+1),COUNTIF(G3:AJ3,"EL"))),"")
AM3AM3=IFNA(INDEX($G$2:$AJ$2,,MATCH("A",$G3:$AJ3,0)),"")
AN3AN3=IFERROR(INDEX($G$2:$AJ$2,SMALL(IF("A"=$G3:$AJ3,COLUMN($G3:$AJ3)-COLUMN($G3)+1),2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
THANKS SIR,
THANKS A LOT
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top