To get last date when an employee was present

[Amitsingh546]

[TABLE="width: 2412"]
<colgroup><col span="3"><col span="3"><col><col><col span="2"><col><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]01-Jul-18[/TD]
[TD="align: right"]02-Jul-18[/TD]
[TD="align: right"]03-Jul-18[/TD]
[TD="align: right"]04-Jul-18[/TD]
[TD="align: right"]05-Jul-18[/TD]
[TD="align: right"]06-Jul-18[/TD]
[TD="align: right"]07-Jul-18[/TD]
[TD="align: right"]08-Jul-18[/TD]
[TD="align: right"]09-Jul-18[/TD]
[TD="align: right"]10-Jul-18[/TD]
[TD="align: right"]11-Jul-18[/TD]
[TD="align: right"]12-Jul-18[/TD]
[TD="align: right"]13-Jul-18[/TD]
[TD="align: right"]14-Jul-18[/TD]
[TD="align: right"]15-Jul-18[/TD]
[TD="align: right"]16-Jul-18[/TD]
[TD="align: right"]17-Jul-18[/TD]
[TD="align: right"]18-Jul-18[/TD]
[TD="align: right"]19-Jul-18[/TD]
[TD="align: right"]20-Jul-18[/TD]
[TD="align: right"]21-Jul-18[/TD]
[TD="align: right"]22-Jul-18[/TD]
[TD="align: right"]23-Jul-18[/TD]
[TD="align: right"]24-Jul-18[/TD]
[TD="align: right"]25-Jul-18[/TD]
[TD="align: right"]26-Jul-18[/TD]
[TD="align: right"]27-Jul-18[/TD]
[TD="align: right"]28-Jul-18[/TD]
[TD="align: right"]29-Jul-18[/TD]
[TD="align: right"]30-Jul-18[/TD]
[TD="align: right"]31-Jul-18[/TD]
[TD]Last date when employee was present[/TD]
[/TR]
[TR]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Weekly Off[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Hi,

Can any one guide me as to how to get last date when an employee was present from above attendance sheet.

Thank You
Amit

 

[Weazel]

maybe something like...

=LOOKUP(2,1/(A2:AE2="present"),$A$1:$AF$1)

 

[Amitsingh546]

Many Thanks Weazel

Can you also please assist me to get first date since employee is continuous absent i.e in below case employee is continuous absent from 28th Jul till month end

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]24-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]25-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]26-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]27-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]28-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]29-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]30-Jul-18[/TD]
[TD="class: xl63, width: 64, align: right"]31-Jul-18[/TD]
[/TR]
[TR]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you also please assist me to get first date since employee is continuous absent i.e in below case employee is continuous absent from 28th Jul till month end

Could we have 4 or 5 more sets of sample data and expected results for this second question?

 

[Amitsingh546]

Welcome to the MrExcel board!

Could we have 4 or 5 more sets of sample data and expected results for this second question?

Hi,

Below is the sample data set.

[TABLE="width: 630"]
<colgroup><col span="9"></colgroup><tbody>[TR]
[TD]24-Jul-18[/TD]
[TD]25-Jul-18[/TD]
[TD]26-Jul-18[/TD]
[TD]27-Jul-18[/TD]
[TD]28-Jul-18[/TD]
[TD]29-Jul-18[/TD]
[TD]30-Jul-18[/TD]
[TD]31-Jul-18[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]26-Jul-18[/TD]
[/TR]
[TR]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]28-Jul-18[/TD]
[/TR]
[TR]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]27-Jul-18[/TD]
[/TR]
[TR]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[TD]29-Jul-18[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi,

Below is the sample data set.

In I2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/((A2:G2="present")*(B2:H2="absent")),$B$1:$H$1)

 

[Amitsingh546]

In I2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/((A2:G2="present")*(B2:H2="absent")),$B$1:$H$1)

Thank you so much!

 

[Aladin Akyurek]

Thank you so much!

You are welcome.