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!

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.

Originally Posted by drewber

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

Let's see if this works.

Originally Posted by AliGW

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","")

This is odd.

OK, thank you

Originally Posted by AliGW

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

Yea, me too. Sorry drewber

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!

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:

=COUNTIF(INDEX($C$3:$C$1000,MATCH(J3,$A$3:$A$1000,0)):INDEX($C$3:$C$1000,AGGREGATE(15,6,(ROW($A$3:$A$1000)-ROW($A$3)+1)/($A$3:$A$1000=J3),2)),"late")

Change columns for the September data accordingly.

Good luck!