Saqib Husain
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Saqib,
If you want one rule for all cells, then consider:
=IF(ISODD(COLUMN(C2)),OR(A ND(--C2>0, D2=""),--C 2>--D2,AND (C2<>"",-- C2>TIME(12 ,0,0)),AND (C2<>"",-- C2<TIME(5, 30,0)),AND (ISERR(--C 2),D2<>"") ),
OR(AND(--C2>0,B2=""),AND(C 2<>"",--C2 <TIME(12,0 ,0)),AND(C 2<>"",--C2 <TIME(5,30 ,0)),AND(I SERR(--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
If you want one rule for all cells, then consider:
=IF(ISODD(COLUMN(C2)),OR(A
OR(AND(--C2>0,B2=""),AND(C
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
ASKER
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