Solved

Highlighting anomalies in attendance register.

Posted on 2013-11-26
3
192 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 80

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now