asked on
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
Times-Sheet-Master.xls
Microsoft Excel
Last Comment
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
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?
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
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?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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
ZZ-Time-Sheet-Master--Revised-March-2015
ASKER
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.
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...
Is this possible?