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.
Welcome Message to New users Spam Sample File

Macro to Daily Attendance.

Discussion in 'VBA Macros' started by Lasantha, Jan 9, 2018.

  1. Lasantha

    Lasantha Member

    Messages:
    80
    Dear Team,


    I am working with our company HR team to generate attendance report of our employees. Could you please help me to create a Macro or working file to capture IN and OUT times, production times , break times and Incomplete entities from attached raw data file. I have attached required template as well.


    · IN and OUT time- (example employee Amila Niroshan 1st IN time should take as IN time(8:16:45 AM), Last out time should take as OUT time(6:17:21 PM)

    · Production time- (Last OUT time deduct by -1st IN Time)

    · Attendance -( if emp has worked more than 8hr , should mark as 1, if production time less than 5hr should mark as 0.5)

    · Breaks ( OUT and IN time between 1st IN and Last out should capture as break times, example emp Amila Niroshan, went outside at 12:16 PM and back to office 2:08PM, so break time is 1:51)

    Ex2 for breaks emp Clinton Sivalingam went outside 9:46PM and Back 10:02, again went outside 11:43PM and back to office 11:48PM ,again 2:37PM and 3:18PM, to total breaks time should capture as 50:22

    · Incomplete those who haven’t punch IN and OUT properly should mark as Incomplete in column K, remarks, no need to calculate production time or break of those employees. But we need 1st In time and Last OUT time of those employees.

    Example, Emp Ranmal Lasantha has 2 IN times 6:37 PM and 7: 28 PM , so this should me mark as Incomplete, but we need 1st IN time 6:37PM and Last OUT time 5:10 AM.

    Example 2, Emp Nelum Pavithra , there is several IN times and Out times.

    Kindly Note we have 2 shift as Day shift and Night shift , Day shift start at 8:30 AM and end at 5:30 PM, then night shift start at 8:00 PM and end at next day 5:00 AM.


    Please let me know if you need further details.


    Thank you very much for your time.

    Lasantha.

    Attached Files:

    Lasantha, Jan 9, 2018
    #1
  2. Lasantha

    Lasantha Member

    Messages:
    80
    Hi Team,

    Can you please help me on this.

    thank you,
    Lasantha.
    Lasantha, Jan 12, 2018
    #2
  3. Logit

    Logit Active Member

    Messages:
    203
    Here is one method :

    Formulas :
    G2 : =IF(F2="","",F2-E2)
    H2 : =IF(G2="","",IF(G2<(8/24),"0.5","1"))
    J2 : =IF(H2="","",(G2-I2))
    K2 : =IF(E2&F2="","",IF(F2="","Incomplete",IF(E2="","Incomplete","")))
    ALL FORMULAS DRAG DOWN THE COLUMN

    Formatting and Entries :
    #2 - Columns E, F, G, I, & J are formatted as CUSTOM / [h]:mm:ss
    #3 - Columns E & F must be entered as : M/D/YYYY H:MM or MM/DD/YYYY H:MM depending on the date.
    #4 - Partial Hours are entered as: 0:15 for fifteen minutes, 0:30 for thirty minutes, 0:45 for 45 minutes.
    #5 - If either E or F are left blank, INCOMPLETE displayed in Column K and highlighted in RED.


    Concerning transferring the RAW data to the IS BLANK worksheet ... hopefully someone else can provide that answer for you.

    Attached Files:

    Logit, Jan 14, 2018
    #3

Share This Page