1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Lasantha, May 16, 2017.

  1. Lasantha

    Lasantha New Member

    Messages:
    23
    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.

    Attached Files:

    ThrottleWorks likes this.
  2. vletm

    vletm Well-Known Member

    Messages:
    2,553
    Lasantha
    You didn't explain what Your term "break" means,
    but no matter ...
    Something like this?
    after pressed [ Do It ]

    Attached Files:

    ThrottleWorks and Lasantha like this.
  3. Lasantha

    Lasantha New Member

    Messages:
    23
    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

    Attached Files:

  4. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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 likes this.
  5. Lasantha

    Lasantha New Member

    Messages:
    23
    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

    Attached Files:

  6. vletm

    vletm Well-Known Member

    Messages:
    2,553
    Lasantha ... hmm?
    There can be some challenge cause MDY-date format with 'textdate'.
    Verify Summary in this version...

    Attached Files:

    Lasantha likes this.
  7. Lasantha

    Lasantha New Member

    Messages:
    23
    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
  8. vletm

    vletm Well-Known Member

    Messages:
    2,553
    Lasantha
    Which rows are missing?
    There should at least one line per employee if data!
    Name .. What would it be connection with Excel?
  9. Lasantha

    Lasantha New Member

    Messages:
    23
    I will review this again and get back to you asap.
  10. Lasantha

    Lasantha New Member

    Messages:
    23
    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.

    Attached Files:

  11. vletm

    vletm Well-Known Member

    Messages:
    2,553
    Lasantha - Your asap ... now good timing
    1) As I wrote #6Reply:
    There can be some challenge cause MDY-date format with 'textdate'.
    2) Use same file I send for You!

    Attached Files:

    Thomas Kuriakose and Lasantha like this.
  12. Lasantha

    Lasantha New Member

    Messages:
    23
    Thank you Very Much for your help
  13. Lasantha

    Lasantha New Member

    Messages:
    23
    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.
  14. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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.
  15. Lasantha

    Lasantha New Member

    Messages:
    23
    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.

    Attached Files:

  16. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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'?
  17. Lasantha

    Lasantha New Member

    Messages:
    23
    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.
  18. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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!

    Attached Files:

    Thomas Kuriakose and Lasantha like this.
  19. Lasantha

    Lasantha New Member

    Messages:
    23
    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.

    Attached Files:

  20. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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?
  21. Lasantha

    Lasantha New Member

    Messages:
    23
    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.

    Attached Files:

  22. vletm

    vletm Well-Known Member

    Messages:
    2,553
    Lasantha
    From photo cannot see 'why' ... that's why file!
    Test this
    1) press [Do It]
    2) SAVE
    3) Send same file back

    Attached Files:

    Thomas Kuriakose likes this.
  23. Lasantha

    Lasantha New Member

    Messages:
    23
    Hi,

    Please find the attached files.

    Lasantha.

    Attached Files:

  24. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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!

    Attached Files:

  25. vletm

    vletm Well-Known Member

    Messages:
    2,553
    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!

    Attached Files:

    Lasantha likes this.

Share This Page