Attendance system

I want the attendance system created, to sort the data in a form from which: Identifiers such as Job ID and name is used as identifier. Then, from the data, sorted and entered in a form or table, there shall be column for sign on/ sign off. From that, the hours worked can be stated and if less than the set shift hours, then the person is marked as late and the hours late are accumulated in the column for deduction.  If 24 hours passes without a registered ID registering time, then they shall be marked as absent. Thus the absent days are also accumulated in the attendance system. If someone works two shifts the same day, then they are entered in a form for overtime, with date and location set as well. Maybe this will be a good guide.

1.       From the unsorted data, filter and import data

2.       Create columns for ID, Name, Date, sign on, sign off time, longitude/latitude.

3.       From the created table, then sort the data based on:

a.       If sign the difference between sign off and sign on time for the same day is less than 12 hours, then, record in a column for lateness in hours.

b. If a registered ID and name misses time for a whole day, over 24 hours, record under absent.

c.    If someone clocks and works at intervals of 12 hours twice in a day, record as overtime.

d. At the end of the sheet, there shall be columns for the total number of days absent, late, which shall be recorded under deductions column.

 d.  Develop a chart showing, lateness, absent overtime.

Sample output of the sorted data table

Job ID	Name	Date	Time sign on	Sign off	Lateness ( min)	Overtime HOURS	Total monthly overtime in days
12341	John Doe	16/12/2016	06:34:05 AM	18:32:05 pm	1	0	2
1233	Rose Mary	16/12/2016	07:34:05 AM	17:32:05 pm	2	0	-4
1233	Mc Steve	16/12/2016	07:34:05 AM	17:32:05 pm	1	0	1

At the end of every month, the accumulated lateness hours is added and converted to days. So if someone has been late for 30 minutes every day for 8 days, then, the last column shall have the total monthly deduction or overtime recorded.