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.
Microsoft Excel

Avatar of undefined
Last Comment
Danny Child

8/22/2022 - Mon
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?
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?
Danny Child

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Pete Edwards

I think this will work, I have written a conditional format to highlight the cell green and its not working can you help.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Pete Edwards

Thanks.  You happen to be able to help me with the conditional formatting request, let me know.
Danny Child

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.
Danny Child

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Danny Child

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...