• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to capture IN , OUT and break time

Lasantha

Member
Dear All,

I need a Macro to identify employees IN and OUT times and total breaks times. I have attached sample file which i extracted from finger print scanner. Also I have given required fields from raw data file. Can you please help me to create a Macro to this.

Thank You.
Lasantha.
 

Attachments

Lasantha

Member
Thank you very much .This is working perfectly with IN and OUT times.
Sorry i didn't mention about breaks.
Please review attached file for more information for break times.
Can you please calculate break times also.

Really appreciate your help on this.
thank you again.
Lasantha
 

Attachments

vletm

Excel Ninja
Lasantha
Are there something challenges with those 'Breaks'?
Screen Shot 2017-05-16 at 20.45.33.png
... Why not ex rows 3-4, 11-12, 23-24? and so on?
Note: My calculations just use Your data as it is!
 

Lasantha

Member
Hi ,
I hope you are doing well.

Thank you very much, Break calculation also accurate. :)

I test this with another data file. There are some records which missing IN or OUT times. Can you please add code to capture these things also.
Ex: Please look in to the RAW 8,23,24, 108 , we have only IN times of those individuals. Can you please include only IN time of those individuals in summary sheet.

Please review attached data file for further information.

Thank you again,
Lasantha
 

Attachments

Lasantha

Member
Hi ,
Thank you for this. I think missing OUT comes also capture accurately.
I will check this another data file and update you.

May I know your name please.

Thank you again.
Lasantha
 

Lasantha

Member
Hi ,
Hope your doing well ,
Today I checked this again with another 2 data file. I have attached it.
I think there is no missing raws in summary sheet, When check with detail file. Please check below 2 points.

  • Breaks and Hours are not capture accurately with sheet 1 data.
  • Breaks and Hours are capture accurately with sheet 2 data.
Could you please look into this issue and edit Macro.

Thank you,
Lasantha.
 

Attachments

Lasantha

Member
Hi,
I need a excel formula to return N th character of a string.
EX: NATURE
I need to display "A" in another cell.
Can you please help me on this.
thank you,
Lasantha.
 

vletm

Excel Ninja
Lasantha
1) Those IN & OUT + extras work only that kind of data You have given.
Extras (breaks & hours) show total values; not daily values!
2) =MID(A1,N,1) , A1 is cell which has You "NATURE" & N is Your Nth character.
 

Lasantha

Member
Hi,
Hope you are going well,
I need your assistant again to add "Status" column after "Hours" column.
I have given below conditions which i need.Also I have attached a data file for you.

  1. Hours 0:00:00 to 4:59:59 "Half Day"
  2. Hours 5:00:00 to 6:59:59 "Less Production Hrs"
  3. Hours 7:00:00 to 7:59:59 " Short Leave"
Could you please help me on this.

Thank you,
Lasantha.
 

Attachments

vletm

Excel Ninja
Lasantha
As I wrote in #14:
1) Those IN & OUT + extras work only that kind of data You have given.
Extras (breaks & hours) show total values; not daily values!

And #11:
2) Use same file I send for You!
If need as it should use Daily values then this needs much more changes!
Are You sure about those 'conditions'?
 

Lasantha

Member
Thank you,
  1. Actually we have 2 shift ( Day and Night) , so we can't take daily values. We can take data in data file, no issue on it.
  2. Don't take daily value, we are run the reports from the system like below.
EX: 05/19/2017 5:00:00 AM to 05/20/2017 5:00:00 AM

  • No issue with condition , you can use it.
Could you please adjust the Macro according above points.

Thank you,
Lasantha.
 

vletm

Excel Ninja
Lasantha - the 1st time is always the 1st time!
So far no matter how many shifts!
If working in Monday ... then working Monday hours!
>> more work for someone else to take care .. but no way! <<
... and of course I add something extra too.
Hours 0:00:00 is impossible value!

And #11: 2) Use same file I send for You!
 

Attachments

vletm

Excel Ninja
How many times do I have to copy next line?
And #11: 2) Use same file I send for You!
I don't use 'PhotoShop' for editing pictures!
Screen Shot 2017-05-22 at 19.52.10.png
... could You use this?
 

Lasantha

Member
Sorry Sir,

I didn't use Photoshop, I just took a screen capture to paint and sent you. I think it is not working in my PC.

please don't miss understand.

Please see attached file. (after I run your Macro)

Thank you,
Lasantha.
 

Attachments

vletm

Excel Ninja
Lasantha
I found it ... but
Your raw datas 'Date/Time' is 'M/D/YYYY'-format and TEXT!
Test this ...
it would set 'Data's H-column to 'DD/MM/YYYY HH:MM'-format and REAL DATE/TIME.
>> Same test as previous! No need to send other files!
 

Attachments

vletm

Excel Ninja
Lasantha
Did You solve that 'date'-mystery there?
Here those dates work as well as possible!
I use here 'dd/mm/yyyy'-format with dates.
Did You check that H-column in 'Data'-sheet.
All dates should look like 12/05/2017 or 13/05/2017!
Your file was 05/12/2017 or 13/05/2017 ... not good!
I found one more possible way to ... set ... that date to correct!
Check that file ... it will rotate date via dd-mmm-yyyy.
ALL DATES HAVE TO BE IN SAME FORMAT!
... or You should change Your 'normal' date-format to dd/mm/yyyy!
 

Attachments

Top