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]

Also i would like to ask how can i highlight the cells or atleast count the number of days with time having within 9:45 PM to 5:45 AM.. For a graveyard pay...


Thanks...

 

[PaddyD]

Harold,

In general, you'll get more answers if you describe your problem in words or use one of the tools to show bits of your worksheet within the post than if you just say 'help - here's a link to my file'. Some board members, me included, have an old-fashioned distrust of opening unknown files.

 

[Harold Garcia]

I'm sorry sir PaddyP... i cannot describe my problem since i cannot picture in mind how to do it... im sure that the file can be trusted or else you can banned me in this sites...
there's a word in the file that can elaborate my problem... Again sorry for my action..

 

[Harold Garcia]

My first problem is how can i convert time in a text format to be converted in a hh:mm AM/PM format?

 

[Harold Garcia]

This is the exact format:

¦20:17 ¦ in General


I want to be in this format:


8:17 pm in Time format

 

[Harold Garcia]

Another thing is, how can i match a data in another data which dont have the exact data...

 

[Harold Garcia]

Well I guess my post is a tough one...

I think i will start to manually check more than 100 employees for 6 months...

 

[NBVC]

Based on your other query: http://www.mrexcel.com/forum/excel-questions/748828-if-conditions-having-value.html#post3679315, the basic formula would be something like:

=IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH($A4,QPRTCON522321!$A:$A,0)),"¦","")),"")

copied across (and formatted as time).

This doesn't consider the Day-off, etc that you had in your other post. I would need more explanation on how you want to check/account for the data...

 

[Harold Garcia]

how will the data automatically transfer on my requested format to a certain employee only?

for example my name...