HELP with counting the times a person is late once past specific Timevalue

Hi,

The first entry of each day would not be taken into account.

What I wanted is the first late entry count "After 8:30" - timevalue is variable because it is dependent on the time the person tapped in each day.

So for example,

01 Feb - If Person A tapped in at 8:31 (time variable), it is considered as 1 count. Any timings thereafter would not need to be counted in because he is afterall late anyway.

Sorry if it is confusing!

Julia

Hi Julia,

I'm not sure if you'll like my idea but I was able to come up with what you want. I'm sure someone would do better but for the meantime, please check what I did.

1. Under Column J: I used the formula VLOOK UP to exclude the NOT LATE and only capture the 1st log-in of the employee.
2. Next step is I used the PIVOT table and USED the DISTINCT COUNT.

Hi Julia,

I saw the error. I forgot that I cannot simply lookup using the Dates because there were different names. I inserted a NEW CODE under column A (Combination of Name and Date) to provide new Identifier.

On my analysis..

Apple = 15 Late
Zion = 8 Late

Thanks

Hi Jarvin,

I counted the number of times Apple is late from her first late entry on every date - turns out to be a total of 19 though.

hmmm...any idea why the real counts doesn't really telly the counts under the vlookup analysis?

Thanks.

Sorry about it. Quite confused.

Hi Julia,

haha.. funny, got it.. even the TIME apparently was duplicated (see 2/1 and 2/2 of apple) both have same log-in time of 8:50:00 AM. Hence, the Pivot just count this as one. Let me amend the formula

Regards,

Jarvin

Hi Julia,

Please see updated table for your reference.

Regards,

Jarvin

Hmmmm... let's see what will be the impact..

Initially, if you'll check the formula, you'll see LEFT(_,7)... if the minimum number would be .375, then we have to adjust it to 5..

Wait..

Hi Julia,

I made some adjustments..

use this updated file.. let me know if you encounter anymore problem.

Regards,

Jarvs

Hi Jarvin,

All looks fine! Thanks.

But are you able to explain how you derive the num. characters (highlighted in red) for

...CONCATENATE(LEFT(A709,3),RIGHT(A709,5)...??

Warm regards
Julia

Hi Julia,

Under column K (VLOOKUP), you'll see that it's a combination of VALUE of the date, name and Date. Now, on the analysis, we used the formula IF(VALUE(LEFT(K7,7))>=0.354166667,"LATE","NOT LATE"). On the formula, take a look on the LEFT(K7,7), it means it's is capturing the first SEVEN characters of the cell. Now, because the error (highlighted in red) only have 5 characters ".0375", the formula that we have is capturing ".0375Zi, therefore, creating an error.

So what I did is to simply put the value that we want to capture (which is the value date) on the last part, so that all I have to do is to use the MID(_,9,15) on the analysis.

Hi Jarvin,

Ah~ That makes sense.

Thanks a lot for the help and I appreciate it!!

Maybe too late but see if this helps "K" is a helper column

Oh sweet, it fits nicely with 1 & 0 as well. Thanks a lot too!!