+ Reply to Thread
Results 1 to 18 of 18

Employee Tardiness Formula Requested

  1. #1
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Employee Tardiness Formula Requested

    hi everyone

    The sample attached is a direct copy of a report I get from our Telecom team showing when an employee logs in to the phone system each day. Employees are to be logged in by 8:00AM, but we give a 5 minute grace period.

    I am looking for a formula that will return "Late", if the FIRST log in time for the specific date is greater than 8:05:00 AM. Some days employees may log in/log out several times, thus the report shows each log in throughout the day.

    For example, the first day on report (8/1/24) has two logins 7:56:27 AM & 2:08:01 PM. I only need the first time for the date to return "Late" if greater than 8:05:00 or leave cell blank.

    One issue I've noted, if it matters, is that Column B is text. I put =Istext(b2) in C2, which shows true. I have tried multiple ways to convert to number, but have not had success. I'm not as well versed in all that.

    As always, I appreciate your help!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Employee Tardiness Formula Requested

    WHERE do you want the formula to be?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Employee Tardiness Formula Requested

    Try this in D2 copied down:

    =IF(AND(OR(A2<>"",B1=""),B2-INT(B2)>0.4),"LATE","")

    I can't test it as my locale is a UK one and seeing your date/time entries as text. If they are real date/time entries, it should work.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Employee Tardiness Formula Requested

    No reply ...

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

  5. #5
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Re: Employee Tardiness Formula Requested

    Hi!

    That one didn't work for me. However, I worked one out that will work, it's not perfect but I can live with it: =IF(AND(TEXT(B2,"hhmm")>="0806",TEXT(B2,"hhmm")<="0906"),"late","")

    I haven't figured out a way to only do the first login on a given date. in the new sample attached here, B49:B52, for example there are multiple logins, 2 fit criteria for late, but I only need the first one (8/9/24 8:16:43 AM) to show "late".

    This goes into another issue I have, which I'll put in another post - wanting to count the number of "late" per agent - but each month the number of rows per agent will be different. Agent38757 has 32 rows in August, but the next month it may be 44 or 24.

    I also realized I uploaded data in my Sample that did not have many "late" times, eg after 8:05am, sorry about that.

    Thanks for your help.
    Attached Files Attached Files

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,779

    Re: Employee Tardiness Formula Requested

    Quote Originally Posted by drewber View Post
    in the new sample attached here, B49:B52, for example there are multiple logins, 2 fit criteria for late, but I only need the first one (8/9/24 8:16:43 AM) to show "late".
    There is no data in this range nor this time you mention

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Employee Tardiness Formula Requested

    My formula does exactly what you show in the new workbook:

    =IFERROR(IF(AND(OR(A2<>"",B1=""),B2-INT(B2)>0.4),"LATE",""),"")

  8. #8
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Re: Employee Tardiness Formula Requested

    Let's see if this works.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Re: Employee Tardiness Formula Requested

    This is odd.
    Attached Files Attached Files

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,779

    Re: Employee Tardiness Formula Requested

    Quote Originally Posted by AliGW View Post
    My formula does exactly what you show in the new workbook:

    =IFERROR(IF(AND(OR(A2<>"",B1=""),B2-INT(B2)>0.4),"LATE",""),"")
    Shouldn't be LATE for rows 13, 17, 19, 21, 28 and 30?

    I can't work on it because doesn't recognize dates in mine regional settings.

    But I think it should work something like:

    =IF(MIN(IF(INT($B$2:$B$33)=B2,$B$2:$B$33)<=(INT(B2)+"08:00")),"LATE","")

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Employee Tardiness Formula Requested

    I'm dropping out - the US format of the dates means I can't test anything properly.

  12. #12
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Re: Employee Tardiness Formula Requested

    OK, thank you

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,779

    Re: Employee Tardiness Formula Requested

    Quote Originally Posted by AliGW View Post
    I'm dropping out - the US format of the dates means I can't test anything properly.
    Yea, me too. Sorry drewber

  14. #14
    Registered User
    Join Date
    08-29-2024
    Location
    USA
    MS-Off Ver
    365
    Posts
    21

    Employee Tardiness Formula Requested

    I will try to explain best as I can.

    I have a report of each agent's daily logins to our system. They are are required to login by 8:05 am or they are considered "late". I have created a formula to calculate late logins (not perfect yet...only need the 1st login...but working on it).

    What I need now: a formula that will calculate the number of "late" for each agent. The issue: Each month, the number of logins will be different for each agent. So can't write something like for Agent3 look in C3:C34 for "late". Because next month Agent3's number of lates would be in C3:C56.

    Fortunately, I think, the report I get puts the AgentID (eg Agent3) at the first row and the last row for that agent. So, my thought was looking in Column A between the first and last Agent ID's in Column C for the number of "late". A little above my brain capacity.

    The attached spreadsheet has August data and I added September's next to it to show how for each month the number of logins will be different. In real life, each month will be on a separate sheet.
    I would be creating a grid where each AgentID goes and there is where the formula would live showing the number of "late" per Agent, (K2 & K3).

    Hope that explains it well enough. Let me know!
    Thanks so much!
    Attached Files Attached Files

  15. #15
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,977

    Re: Employee Tardiness Formula Requested

    Since post #7 has a file attached and a complete problem description, I just started there rather than wending my through the entire thread from the beginning.

    First, you are using merged cells to identify the agent, which is a practice that causes all kinds of problems. Your problem becomes trivial if you put the agent ID with each timestamp.

    Your logic for identifying lateness would be better off using time values rather than converting to text. Your timestamps are text. These should be date/time values. Since you seem to not have control over where these come from, I have added a column to convert to date/time. That makes any downstream calculations much easier.

    In real life, each month will be on a separate sheet.
    It's better to show us the real-life version, rather than having us prepare a solution that might not work in the real file.

    Your formula considers any time that is between 8:06 AM and 9:06 AM inclusive to be a late login. It seems to me that the actual definition of late is "a login is late if it is the first login of the day and occurs later than 8:05 AM". Your formula missed a late login for Agent 3 on 8/8, and recorded false ones on 8/9 and 8/12, and many others.

    Row 105 does not have a time.

    See sheet Solution.

    You insert blank rows between each day which might enhance appearance, but complicates your data entry. I proposed a different way to do that in Solution 2 where there are no blanks and alternate days are shaded.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  16. #16
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    1,396

    Re: Employee Tardiness Formula Requested

    Hi.

    In this formula in cell K3, copied down to K4, I use INDEX, combined with MATCH & AGGREGATE functions to create dynamic ranges with as many rows as the agent titles show in respective column.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change columns for the September data accordingly.

    Good luck!
    Attached Files Attached Files

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2410
    Posts
    13,580

    Re: Employee Tardiness Formula Requested

    I stopped reading after post #11 and decided to post what I have. It uses 3 helper columns.

    In C2 to isolate the date part.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In D2 to determine where the first entries of the days are.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In E2 the late time deadline.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in F2 to determine if "Late".
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  18. #18
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,625

    Re: Employee Tardiness Formula Requested

    I would give 2 options: one with helper column D and other w/o

    Focus is only on the time, regardless of the date format (US or UK).

    C3:
    Please Login or Register  to view this content.
    D3:
    Please Login or Register  to view this content.
    Then K3 drag down
    Please Login or Register  to view this content.
    W/o helper column D:
    L4 (or the last Agent in list):
    Please Login or Register  to view this content.
    Then L3 copy up to top
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 02-03-2020, 03:08 AM
  2. IF formula to determine tardiness after 8:00 AM
    By chongmoo in forum Excel General
    Replies: 8
    Last Post: 02-13-2019, 12:16 PM
  3. if then formula help requested.
    By corinthian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2015, 07:32 PM
  4. Formula Requested
    By Willy Billy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-14-2015, 12:50 AM
  5. Employee Tardiness based off start time
    By 180drft in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 03:54 PM
  6. Employee Tardiness Clock Time >5 Mins
    By edwyer247 in forum Excel General
    Replies: 1
    Last Post: 06-02-2012, 12:37 AM
  7. Help Requested on Formula
    By Rich Rosier in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2006, 04:10 PM

Tags for this Thread

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