convert non data base to data base format using formula

**murarihyd** · 02-19-2011, 05:33 PM

Dear Members,

we are using attendance sheet of employees in non-data base formate and would like to convert it in to data base format using formula.

in the attached example, the sheet with "attendance" show the employees present date in the month of january.

i would like to convert to data base format as per the sheet "time sheet". pls note the time in and time out are randomly entered and we need to ensure that there is no over time.

i want formulas to create this "time sheet" columns linked to attandance sheet.

pls guide me.

**JBeaucaire** · 02-20-2011, 09:33 AM

On the TIME SHEET, add this formula to L2 and copy the cell down the data set to add a unique indexing KEY:

=B2&"-"&INT(D2)

On the ATTENDANCE sheet put this formula in C2 and copy down/across the table:

=INDEX('TIME SHEET'!$F:$F, MATCH($A2&"-"&C$1, 'TIME SHEET'!$L:$L, 0))

**murarihyd** · 02-20-2011, 01:04 PM

dear JBeaucaire

thanks for your reply. pls note i want the result other way. pls note i want to develop the time sheet from the attendance sheet.

if the employee is present, their in and out time are manually entered. to explain more clearly.

i want following.

1. in the Time sheet, i need to enter formulas in each column and on dragging it, the deta linked to attendance should be populated.

hope i am clear

**JBeaucaire** · 02-20-2011, 02:44 PM

There is information on the TIME SHEET that is not present in the ATTENDANCE SHEET, specifically actual IN/OUT times. You will need to rectify your sample sheet so that all information in your TIME SHEET is fully explained exactly where/how it is derived from the ATTENDANCE SHEET and nothing else.

**murarihyd** · 02-21-2011, 09:50 PM

dear all,

posting a revised file. at present we are maintaining the record as per "attandance" sheet. I want to make it in to data base format as shown in the TIME SHEET

pls guide me