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
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Possible conditional formatting formulas (may be copied down and across):
Incoming Time In 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<>""))
Outgoing Time in D2
=OR(AND(--D2>0,C2=""),AND(D2<>"",--D2<TIME(12,0,0)),AND(D2<>"",--D2<TIME(5,30,0)),AND(ISERR(--D2),C2<>""))
AttendanceQ28304946.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerAuthor Commented:
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
byundtMechanical EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.