Timesheet

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.
Times-Sheet-Master.xls
Pete EdwardsAsked:
Who is Participating?
 
Danny ChildIT ManagerCommented:
0
 
Pete EdwardsAuthor Commented:
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?
0
 
Danny ChildIT ManagerCommented:
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?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Pete EdwardsAuthor Commented:
I think this will work, I have written a conditional format to highlight the cell green and its not working can you help.
ZZ-Time-Sheet-Master--Revised-March-2015
0
 
Pete EdwardsAuthor Commented:
Thanks.  You happen to be able to help me with the conditional formatting request, let me know.
0
 
Danny ChildIT ManagerCommented:
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.
0
 
Danny ChildIT ManagerCommented:
0
 
Danny ChildIT ManagerCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.