To All Excel Experts

[Harold Garcia]

Good day;

Again I would like to ask all the help of all Excel Guru to please help me to create a formula or VBA regarding the time in and time out of some employee. Pasted are the link below on the files i need to work.

https://www.dropbox.com/s/swas3chrxfip7p4/TIME IN - TIME OUT.xlsx or
https://www.dropbox.com/s/swas3chrxfip7p4/TIME IN - TIME OUT.xlsx

Thanks and more Power...

 

[Harold Garcia]

Sir NBVC,
It only takes the time in? how bout the time out?
and the other data like Day-off, SL,Late etc...
can i add the formula on the other thread?

 

[NBVC]

This part: INDEX(QPRTCON522321!B:B,MATCH($A4,QPRTCON522321!$A:$A,0) looks for the name in A4 in this case; DELA CRUZ, ARNOLD T and finds it in the first sheet, then pulls the time from column B, as you copy across the formula, you get time from column C, D, etc....

Try putting the formula in B4 of Attendance sheet, copy it down and copy it across (you may have to format as Time if you are seeing decimal values).

 

[Harold Garcia]

Im also having a problem on a longer names likes, Mr. Hipolito and Ms. Chung... it cannot capture the data...

 

[NBVC]

I don't see those names in the main sheet.

 

[Harold Garcia]

in A10070 Hipolito, Marcos Roe only... does it need an exact match?

 

[Harold Garcia]

How about the Time out of the employee?

 

[NBVC]

in A10070 Hipolito, Marcos Roe only... does it need an exact match?

With current formula, yes. But if you can use just last name (if there are no two people with same last name), then we can use a wildcard search for Hipolito only.

How about the Time out of the employee?

Not sure what you mean?

 

[Harold Garcia]

The upper time is the time-in
the lower time is the time out

it did not capture the time out of the employee

thanks

 

[Harold Garcia]

With current formula, yes. But if you can use just last name (if there are no two people with same last name), then we can use a wildcard search for Hipolito only.



Not sure what you mean?

Sir there are big possibiities of having the same last name... how bout having the nearest match if the name is too long

 

[Harold Garcia]

remember this formula sir NBVC
=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(J4,"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(J4,"¦","")),""))

L-0001 means having a late of 1 min etc

SL---01 meaning Absent

Accum. DO means ADO

here are some data doesn't work