Consolidation of Time attendance in Excel 2013
Dear Sir
I have an excel sheet for the time attendance hours of my labours from diffrent projects. i need to make sure that there is no double entry on the same date in the sheet before i am sending this report to Payroll (example: "X" is working in "A" Project from 01-01-15 up to 12-01-15. 13-01-15 he transfered to another project and continuing) i need to varify that a double entry is not happened on the 12-01-15 & 13-01-15)
kindly find the attached file for better understanding
Thank you & Best Regards
Probably a neater way to do this
Ok this seems messy to me but here is my solution for what it's worth.
I'm making a couple of assumptions as follows: your dates 1 to 31 are in E2:AI2 and your employee no's start in B3 and work down. if this is incorrect please adjust the below accordingly.
Highlight the area E3:AI1000 (or wherever your last row of data is.
Go to conditional formatting - new rule - Use a formula to determine which cells to format - format values where this formula is true:
=IF(SUMPRODUCT(($B$3:$B$1000=$B3)*(E$3:E$1000))/E3>1,1,0)
Select Format - Fill and select the red square. This will make any entries on the same day for the same employee no. become a red cell
Hope that helps
Mark