Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Highlighting anomalies in attendance register.

Posted on 2013-11-26
3
Medium Priority
?
205 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
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

773 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