• 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

  • Data file.xlsx
    17.4 KB · Views: 32
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

  • Data file.xlsx
    18.6 KB · Views: 21
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!
 
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

  • Data file 2.xlsx
    13.4 KB · Views: 16
Lasantha ... hmm?
There can be some challenge cause MDY-date format with 'textdate'.
Verify Summary in this version...
 

Attachments

  • Data file.xlsb
    30.4 KB · Views: 16
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
 
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

  • sheet 1.jpg
    sheet 1.jpg
    318.3 KB · Views: 10
  • Data file 3.xlsx
    37.1 KB · Views: 8
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.
 
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.
 
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

  • Data file 4.xlsx
    22.1 KB · Views: 5
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'?
 
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.
 
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

  • Data file.xlsb
    38.5 KB · Views: 7
Thank you,

Could you please check with above data file. (data file 4),

I have noticed some issue with hours.

Please see the attached screen capture.

Thank you,
Lasantha.
 

Attachments

  • Data file 4.xlsx
    22.1 KB · Views: 5
  • Summary 5_22_2017.jpg
    Summary 5_22_2017.jpg
    511.1 KB · Views: 6
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?
 
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

  • Data file.xlsb
    36 KB · Views: 2
Hi,

Please find the attached files.

Lasantha.
 

Attachments

  • Data file (1).xlsb
    38.3 KB · Views: 5
  • Data file 4.xlsx
    22.1 KB · Views: 1
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

  • Data file (1).xlsb
    34.1 KB · Views: 5
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

  • Data file.xlsb
    42.9 KB · Views: 27
Back
Top