conditional formatting to highlight the last data

[krazyhype19]

Hi,

I have this formula in conditional formatting to get the first entry:

Please Login or Register  to view this content.

Now, how can I format to know the last exit?

Column R is the date and A is the name of a person.

[misrasomendra]

Hi,

How about uploading a sample file.

[krazyhype19]

In my example here's my formula to highlight the first entry:

Please Login or Register  to view this content.

[krazyhype19]

As you have notice the result was not that accurate for the formula i have for the first entry. There were incorrect highlighted item specially if the person is on 8pm shift... I want that also to be fixed.

[krazyhype19]

Please consider that the formula for the last exit must also be applicable if a person rendered overtime. For example a person's shift is 2PM, so the end of his shift would be 11pm but his last exit was 12AM onwards.

[misrasomendra]

Hi,

See the file. Access sheet has CF.
Step 1: Sort the data as shown in file. Level 1: Name ; Level 2: Time-In/Time Out.
Step 2. Select the range from A3 to last cell.
Step 3. Apply the formula as given in CF.

Note1: CF is volatile and over such a large range with so much complexity It will turn your file slow.
Note 2: I had not checked the entire CF over the entire data, but did some random checks which come out to be right . So check it.

[krazyhype19]

Hi misrasomendra,

I have check the file and the CF applies on the wrong entry for the person in 8pm shift. shift is at column I. For example, Christine Ling. On Sep 1, her first entry is correct but she does not have to exit at Sep 1 11:30PM and started her next shift at Sep 2 12:12 AM because she is supposed to login at around 8pm again on Sep 2. The correct last exit should be row 1062 which is Sep 2 6:15AM for her Sep 1 shift. and her first log in for Sep 2 is 8:01PM on row 1063.

It has something to do with calculating the 9hour shift... But I am not sure how to do it... You may add another column for a condition if needed.

[misrasomendra]

I did not understood , you are saying CL first entered in office on Sep 1 2014 @ 07:40 Row 1028 and exit at 02 Sep @ 06:15 row 1062. That would make her shift to 22:35 mins long. I am confused on this point.