Link to home
Start Free TrialLog in
Avatar of Pete Edwards
Pete Edwards

asked on


I have a mock file I created for our shop that I was hoping you could help me out with a key formula.  I would like the formula to be written in the row highlighted yellow with a comment box I have wrote in the Payroll tab.  What I want the formula to do is to take the total hours reported for the day, whereas they clocked in, and clocked out for lunch, and than clocked back in, and than clocked out for the day.  On Monday, this is 10 hours.  I want the 10 hours guaged against the tab that is called Time Sheet.  This tab goes into the detail of the day, it should add up to what they are reporting on the Payroll tab to be paid.  I just want some form of formula written that gives our clerical person an "error message" of some sort so they can pass the file back to the supervisor and tell them they cannot be paid for the hours until they update what the hours were assigned to.
Avatar of Pete Edwards
Pete Edwards


What I was hoping you could do is to write a formula whereas the two different components in both tabs needing tabulated are added up and if they equal, than to add up the pay on the payroll, if they do not equal, than provide in the shortage/overage as the end result.  I would like this formula setup as a conditional format, whereas on the nonequal "true" concept to highlight the cell "red".  

Is this possible?
Avatar of Danny Child
OK, looks like your Time Sheet tab deals with the total days work - is that right?  ie for Mon 9/3 it's 4.25 hours?

But the employee is down for 10 hours?

What error do you want to trap?  Payroll sheet time > Time Sheet time - or vice versa - or both?

I've done something for the former
I've added a couple of helper cells on the Time Sheet page, with cells B2 and C2 getting the date and time total for that date.  This is so I can build an extended Vlookup table running down the page.  These helper cells are repeated for every day.

Then cell C24 has this formula:
=IF(C23>VLOOKUP(C10,'Time Sheet'!B:C,2,FALSE),"Check allocation!","")
which just compares the total time for that day (cell above) against a vlookup of Time Sheet time for that day.

Is this on the right track?
Avatar of Danny Child
Danny Child
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think this will work, I have written a conditional format to highlight the cell green and its not working can you help.
Thanks.  You happen to be able to help me with the conditional formatting request, let me know.
Not a problem.
What I've done is - select 1 cell.  Cleared all previous formats from it.

Then, Conditional Formatting.. Highlight  Cell Rules.. Text That Contains.. and then entered Good as the text, and choose Custom Format (Green cell fill, Black text).
Repeat the above for Incorrect, but chosen the stock format of Light Red Fill, Dark Red Text.
file upload tool seems a bit bonkers today... when it downloads my file, rename it.  Get rid of the .zip on the end, and change it to .XLSX (not just plain .XLS).  Should be OK after that...