Attendance format

[Devdatta Singasane]

Ref attached file, need working formula for finding out last present day of employee.
I have tried max formula with array to find out last "P" but it will not be helpful if resign status comes earlier. so i need first "last" after first "r".

 

[Khalid NGO]

Hi and welcome to the forum Chandoo.org.

If I understood it correctly, you can use the following formula with CSE:

=MIN(IF(C3:AE3>=MIN(IF(C4:AE4="R",C3:AE3)),IF(C4:AE4="P",C3:AE3)))

Enter with Ctrl+Shift+Enter.

Regards,

 

[tomas]

Hi

my formula would be

=MAX((INDEX($C$4:$AE$4,MATCH("R",$C$4:$AE$4,0)):$AE$4="P")*(INDEX($C$3:$AE$3,MATCH("R",$C$4:$AE$4,0)):$AE$3))

with CSE

 

[tomas]

Hi and welcome to the forum Chandoo.org.

If I understood it correctly, you can use the following formula with CSE:
=MIN(IF(C3:AE3>=MIN(IF(C4:AE4="R",C3:AE3)),IF(C4:AE4="P",C3:AE3)))

Enter with Ctrl+Shift+Enter.

Regards,

Hi Khalid your formula gives result 4.2.2015 and mine 11.2.2015. without inspecting your formula dont you look first occurence of P after first R?

 

[Khalid NGO]

Hi Khalid your formula gives result 4.2.2015 and mine 11.2.2015

Hi Tomas,
Yes there are some confusions

last working day should be last "P" after first "R"
first "R" starting form = 01/Feb/2015 to 03/Feb/2015
so the last "P" would be 04/Feb/2015

isn't it?

 

[Devdatta Singasane]

Thanks for prompt replay.

But not getting the correct date.

Correct last working date should come 29th january 2015.

 

[Khalid NGO]

first occurence of P after first R?

Yes I think entries in the layout are creating confusion + expected result is not mentioned clearly.
Let OP decides, If you are right in that case, here is the revised one:
=MIN(IF(C4:AE4="R",MAX(IF(C4:AE4="P",C3:AE3))))
with CSE

 

[Devdatta Singasane]

Regret for confusing language. First R is on 1st Feb, his last present day i.e "P" should be 29th Jan.

 

[tomas]

This will get 29.1

=MAX(($C$4:INDEX($C$4:$AE$4,MATCH("R",$C$4:$AE$4,0))="P")*($C$3:INDEX($C$3:$AE$3,MATCH("R",$C$4:$AE$4,0))))

 

[Khalid NGO]

Here is mine

=MAX(IF(MIN(IF(C4:AE4="R",C3:AE3))>=C3:AE3,IF(C4:AE4="P",C3:AE3)))
with CSE

 

[Khalid NGO]

=MAX(IF(MIN(IF(C4:AE4="R",C3:AE3))>=C3:AE3,IF(C4:AE4="P",C3:AE3)))

It can be shorten, let me try...