Creating Attendance Sheet with Form Results Help

[osama3d]

Hello Members,

My purpose is to create an automatic attendance sheet based on the results of an excel form.

In the attached sample, Column A and Column B ( "Excel Form Results" Worksheet) are results exported from an excel form. Now what I want to do is:

1.) In the "Attendance" Worksheet, I want to input a text "P" if the individual is Present, or in other words who as submitted the form, and the text "A" if the form is not submitted, or in other words, absent. (Example Row 21 to 25 in "Excel Form Results" Worksheet).

2.) Furthermore, if the individual is present, then I need to know if that individual is on time. Sample sheet shows a reporting time column (Column C). If the individual submits the form within 5 minutes of the reporting time, text should remain "P", and if the the time is more than or equal to 5 minutes, then text should return "L".

3.) If the text is returned "L", in another cell it should calculate the number of minutes late.

4.) This all calculation needs to be done on the "Attendance" Worksheet, with all the names in Column A, and the texts populated under each date (Cloumn B onwards).

I am able to work out the "P" and "A" function and not the "L" one, so need your help on this.

Kindly do also note that row 2 to 4 (Form results tab) has reporting time at 00:00:00, so the date changes, and I want the results to be calculated on the previous date column.

Need your experts opinioin on how can I achive this.

[Keebellah]

Hi, welcome to the forum.
In principle the question is clear and with a little knowledge you could add the formulas yourself, that is if you have some basic knowledge how to use Excel without complicated VBA code. simple formulas can take care of this and with just a little effort all what you need to know is available (Excel Help, Google, you just need to invest time and patience and imagination)
I've downloaded your file and will see what I can do and if I understand your request correctly.

[Keebellah]

Try this and see, hope you understand the formulas, and ... make sure the column A is correctly formatted as DATE

BTW, you mention sheet names that you have not provided, next time when you post attachments make sure they comply with what you're asking

[osama3d]

Thank you Keebellah,
It is what I am looking for.

And I apologize for not giving the correct sheet names.

I tried using the data with different dates, but couldnt get through the formula properly. Would you check it out?

Now the formula should look at the date (D1,F1 and so on) and search in the list (Column A) and return the answer.

[Keebellah]

I suggest you look at what the formulas I gave you do, what you need to do is make sure that the reference to the cells is correct
I will try and look later this evening when I have some more time

[Keebellah]

You cannot do what you want because you don't have the correct values, the column cannot be a date it's a timestamp and that varies per person as well as the reporting time
You will have to fill the table and once the data is filled create a report from that for a particular day in another worksheet
I suggest a Listobject (Table) in the Attendance sheet and once completed you can play around with a pivot table or index and match formulas to create a specific report for a particular date
With macro's more can be done but then you need to come with a clear and concise explanation of what it is you really want.