Required excel formulation for attached sheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-1520185%22%20slang%3D%22en-US%22%3ERequired%20excel%20formulation%20for%20attached%20sheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520185%22%20slang%3D%22en-US%22%3E%3CP%3EHello..%3C%2FP%3E%3CP%3EI%20need%20formula%20at%20the%20sheet%202%20so%20that%20the%20attendance%20can%20be%20achieved%20by%20denoting%20%22P%22-%20present%20or%20%22A%22%20.%20The%20data%20can%20be%20extracted%20from%20sheet%201.%20The%20in%20time%20has%20been%20denoted%20by%20date%20in%20sheet%201%20.Please%20help.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20excel%20sheet%20has%20been%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1520185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520218%22%20slang%3D%22en-US%22%3ERe%3A%20Required%20excel%20formulation%20for%20attached%20sheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727643%22%20target%3D%22_blank%22%3E%40abhishek2000%3C%2FA%3E%26nbsp%3BHave%20you%20considered%20using%20a%20pivot%20table%3F%20See%20attached%20Sheet4.%20This%20example%20marks%20present%20with%20%221%22%20and%20leaves%20absent%20blank%2C%20giving%20an%20instant%20overview.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello..

I need formula at the sheet 2 so that the attendance can be achieved by denoting "P"- present or "A" . The data can be extracted from sheet 1. The in time has been denoted by date in sheet 1 .Please help. 

The excel sheet has been attached.

1 Reply

@abhishek2000 Have you considered using a pivot table? See attached Sheet4. This example marks present with "1" and leaves absent blank, giving an instant overview.