Last Working day Formula in attendance sheet.

[RUPESH16]

Hi Sir,

pls find the attachment of attendance sheet.

in this sheet can you please tell me how to find out the last working day of the employee

pls suggest me.

Regards
Rupesh

 

[Faseeh]

Please try this with ctrl+shift+enter

=IF(BP3="Active","Active",INDEX($L$2:$AO$2,MAX(IF(L3:AO3=1,COLUMN(L3:AO3)-COLUMN(K3)))))

Drag down.

 

[NARAYANK991]

Hi Rupesh ,

See if this is OK.

Narayan

 

[RUPESH16]

=IF(BP3="Active","Active",INDEX($L$2:$AO$2,MAX(IF(L3:AO3=1,COLUMN(L3:AO3)-COLUMN(K3)))))

Drag down.[/quote]


thank you so much sir,

but small concern if suppose on 25th is his "wo" and 26 onwords "RES", then last working day should be 25th suppose to come, since we need to pay "wo" salary.

 

[NARAYANK991]

Hi Rupesh ,

See this file.

Narayan

 

[RUPESH16]

Please try this with ctrl+shift+enter

=IF(BP3="Active","Active",INDEX($L$2:$AO$2,MAX(IF(L3:AO3=1,COLUMN(L3:AO3)-COLUMN(K3)))))

Drag down.

sir pls update.

 

[Faseeh]

means in whatever comes before RES should be paid, either it is 1 or wo? is that correct?

 

[RUPESH16]

means in whatever comes before RES should be paid, either it is 1 or wo? is that correct?

yes exactly,


but no need to pay for ”-1” only.

 

[Faseeh]

Hi Rupesh,

Please see attachment.

 

[RUPESH16]

Hi Rupesh,

Please see attachment.

Hi Faseeh,

pls find attachment what i want the result which is highlighted with a green color.

pls check it once...

 

[Faseeh]

Let me reach home.

 

[RUPESH16]

oh..! ok

 

[NARAYANK991]

Hi Rupesh ,

See this file.

Narayan

 

[RUPESH16]

Faseeh & Narayan Sir very very thank you somuch..

fourmula is ok, But I am unable to understand little bit what is MMULT..

anyway it’s OK.

Sir i want to learn more excel knowledge can you suggest me.

what will be the best platform/Institute.

 

[NARAYANK991]

Hi Rupesh ,

What ever I have learnt , I have learnt from forums like Chandoo's ; others such as MrExcel , Ozgrid , and blogs mentioned in the following link will all help :

http://blog.cometdocs.com/the-best-blogs-and-youtube-channels-for-learning-ms-excel

Narayan

 

[RUPESH16]

Hi Sir,

Sorry ..

again i have some concern..

Pls find the attachement in that attachemnt i have mentioned some code for present & absent, so now my concern is that if an employee is inactive but before inactive employee was on leave so will mentiond i.e "PL" ,"CL" or below codes for the below codes/ days will him. so how can i get
their Last working day.

pls suggest me.

Codes For Present
1
0.5
WO
CO
PL
SL
CL
0.5 & 0.5PL
0.5 & 0.5CL
0.5 & 0.5SL