Solved

Highlighting anomalies in attendance register.

Posted on 2013-11-26
3
201 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Saqib Husain, Syed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39679919
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
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 39679948
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 39680811
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
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question