Avatar of Saqib Husain
Saqib HusainFlag for Pakistan asked on

Highlighting anomalies in attendance register.

I have attached a typical attendance register.

This register contains a lot of erratic entries which I want highlighted with the help of conditional formatting. The conditions are based on the following guidelines: (suggestions are also welcome)

All entries including times are texts (I do not want to change this)
The time entries have been produced by a fingerprint reader
The non-time entries have been entered manually
For a certain date if there is an incoming record and no outgoing record then highlight
Also highlight if it is the other way round
If the incoming time is later than the outgoing time then highlight
If the incoming time is later than noon then highlight
If the outgoing time is earlier than noon then highlight
If any time is between midnight and 0530 then highlight
If one is text and the other is not blank then highlight
Attendance.xls
Microsoft Excel

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Saqib Husain

Hi, Brad,

Actually I was looking for a more generic solution where I could use just one formula for the entire block. But this serves my purpose for the moment so I shall just close this here. Thanks for the help.

Saqib
byundt

Saqib,
If you want one rule for all cells, then consider:
=IF(ISODD(COLUMN(C2)),OR(AND(--C2>0,D2=""),--C2>--D2,AND(C2<>"",--C2>TIME(12,0,0)),AND(C2<>"",--C2<TIME(5,30,0)),AND(ISERR(--C2),D2<>"")),
OR(AND(--C2>0,B2=""),AND(C2<>"",--C2<TIME(12,0,0)),AND(C2<>"",--C2<TIME(5,30,0)),AND(ISERR(--C2),B2<>"")))

It's the same formula as previously suggested, but placed inside an IF function so it can apply different tests to incoming (odd columns) and outgoing (even columns). Because the addressing has to be relative to cell C2, you have to shift the addresses for the outgoing part of the formula one column to the left (C2 instead of D2, B2 instead of C2).

Brad
AttendanceQ28304946.xls
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck