+ Reply to Thread
Results 1 to 8 of 8

Time calculation for attendance, tardy/late to work?

  1. #1
    Hanr3
    Guest

    Time calculation for attendance, tardy/late to work?

    I need to wirte an if statement that looks at a time stamp with date/time and
    compatare it to the employees start time. If the employee comes in past their
    start time, tardy, if before OK. Employees have all sorts of start times and
    cross midnight.
    Thanks,
    --
    Life is an adventure, are you living it?

    These are just my opinions, please feel free to correct them if they are
    wrong.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    Your criteria (first argument) in your IF statement could be a simple subtraction of the times if you make your stamp date and time.
    Martin

  3. #3
    Hanr3
    Guest

    Re: Time calculation for attendance, tardy/late to work?

    I can get it to subtract the two numbers and come up with a numeric or time
    formatted answer. I seem to be missing some important detail to make the IF
    statement work right.

    For example.
    Log in time Reg. Start Time Late or not
    5/1/06 7:02 7:00 ???
    What formula could I use to get the answer? I can get the 2 minute
    difference, or the 23:58 minute difference, but I cannot get a True or False
    answer in my IF statement to work. I can get half of it, reverse the equation
    and get the other half, but I cannot get it to work in both cases.

    --
    Life is an adventure, are you living it?

    These are just my opinions, please feel free to correct them if they are
    wrong.


    "mrice" wrote:

    >
    > Your criteria (first argument) in your IF statement could be a simple
    > subtraction of the times if you make your stamp date and time.
    >
    >
    > --
    > mrice
    >
    > Research Scientist with many years of spreadsheet development experience
    > ------------------------------------------------------------------------
    > mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
    > View this thread: http://www.excelforum.com/showthread...hreadid=540312
    >
    >


  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    You could test the value of your subtraction for being greater than or less than zero and get your tru or false that way.

  5. #5
    Simon G
    Guest

    Re: Time calculation for attendance, tardy/late to work?

    Try this equation:

    =IF(B7-A7>0, "late", "ok")

    eg:

    Due time Start time result
    7:00:00 6:58:00 ok
    7:00:00 7:00:00 ok
    7:00:00 7:00:01 late

    Have your employee name (or ID) and dates as separate columns (better for
    sorting etc.)

    Format your A & B Columns as time (hh:mm:ss), you can subtract either one
    from the other, but you have to ensure that you don't result in a negative
    time, as Excel won't like it. (Lets be honest you can't actually have
    negative time)

    "mrice" wrote:

    >
    > You could test the value of your subtraction for being greater than or
    > less than zero and get your tru or false that way.
    >
    >
    > --
    > mrice
    >
    > Research Scientist with many years of spreadsheet development experience
    > ------------------------------------------------------------------------
    > mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
    > View this thread: http://www.excelforum.com/showthread...hreadid=540312
    >
    >


  6. #6
    Hanr3
    Guest

    Re: Time calculation for attendance, tardy/late to work?

    Thanks for your help so far.
    This is what I have as of now.
    I had to create another column and formatted as hh:mm Then insert the
    formula MOD(B2,1). This gave me the time value of the starttime (column B2)
    which was Date/Time and pulled from the timeclock application.
    Then for the If statement it looks like so.
    IF((F2=G2),"OK",IF((F2<G2),"Early",IF((F2>G2),"Tardy",)))
    F=Mod(starttime,1)
    G=Employee daily start time.

    Now the only issue sleft to resolve is the seconds. I need to round them
    down in column F. MOD(B2,1) is the current formula in column F.
    Any ideas?
    Or would it be better to add 1 minute to the employee start time?

    --
    Life is an adventure, are you living it?

    These are just my opinions, please feel free to correct them if they are
    wrong.


    "Simon G" wrote:

    > Try this equation:
    >
    > =IF(B7-A7>0, "late", "ok")
    >
    > eg:
    >
    > Due time Start time result
    > 7:00:00 6:58:00 ok
    > 7:00:00 7:00:00 ok
    > 7:00:00 7:00:01 late
    >
    > Have your employee name (or ID) and dates as separate columns (better for
    > sorting etc.)
    >
    > Format your A & B Columns as time (hh:mm:ss), you can subtract either one
    > from the other, but you have to ensure that you don't result in a negative
    > time, as Excel won't like it. (Lets be honest you can't actually have
    > negative time)
    >
    > "mrice" wrote:
    >
    > >
    > > You could test the value of your subtraction for being greater than or
    > > less than zero and get your tru or false that way.
    > >
    > >
    > > --
    > > mrice
    > >
    > > Research Scientist with many years of spreadsheet development experience
    > > ------------------------------------------------------------------------
    > > mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
    > > View this thread: http://www.excelforum.com/showthread...hreadid=540312
    > >
    > >


  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    Maybe you could do something with the TimeSerial Function populating it with just the hours and minutes.

  8. #8
    Registered User
    Join Date
    12-08-2015
    Location
    manila
    MS-Off Ver
    microsoft office 2010
    Posts
    5

    kindly help me

    hi, i need help,,, i need a formula that calculates the employees late and have deduction.
    i this case if an employee time until 10:15 am there no deduction, beyond 10:16-12:00 noon 50 will be deducted in their salary, then beyond 12:01 it will consider as half day and automatic deducted 200, absent will deduct based on his salary range per day,,,would some one can help me to have a formula on,, kindly email me at [email protected] or u can response on this thread,,thank you so much
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1