Help With Employee Timesheet

[Farrar35594]

I will be moving from my Industrial Eng. position back out onto our shop floor to assume the duties of 2nd Shift Supervisor. All of our supervisors currently use a paper book and pencil to record time. Many years ago when I previously supervised, I used a simple spreadsheet to record time and emailed that to HR every Monday. The old sheet that I have did not address my new issue which is:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a sheet with the days of the week across the top. Into each cell for each day, I will enter hours worked. I have applied this formula:<o></o>
<o></o>
=IF(D3>8,D3-8,0)+IF(E3>8,E3-8,0)+IF(F3>8,F3-8,0)+IF(G3>8,G3-8,0)+IF(H3>8,H3-8,0)+I3+J3<o></o>
<o></o>
To check each days' hour total and add up all overtime hours. Any hours worked over 8 in a day is considered overtime in our facility whereas most simply have anything over 40 hours. The formula above works fine UNTILL I type into that cell the word VACT to indicate that the employee took vacation time. I must at the end of the week turn in all regular hours, overtime hours, vacation hours, and hours missed. <o></o>
<o></o>
Can I amend the formula above to ignore the VACT entry?<o></o>
<o></o>
I am assuming that when it comes time to add up all vacation hours, I can use an IF statement to check Monday-Friday and total my Vacation time.<o></o>
<o></o>
I am assuming {again} that the best way to calculate time missed will be an IF statement which checks each day, determines if the hours are less than 8, and then totals those in a column for each employee?<o></o>
<o></o>
Thank You in Advance for your help.<o></o>
<o></o>
Jeff F.<o></o>
<o></o>
<o></o>

 

[DauntingGecko]

=SUMIF(D4:D9,">0")

Change D4:D9 to the range of your cells. It only sums if its greater than zero (hence ignoring text).

 

[bryonwoods30]

use this formula instead, it checks to make sure it is a number first

=IF(AND(ISNUMBER(D3),D3>8),D3-8,0)+IF(AND(ISNUMBER(E3),E3)>8,E3-8,0)+IF(AND(ISNUMBER(F3),F3>8),F3-8,0)+IF(AND(ISNUMBER(G3),G3>8),G3-8,0)+IF(AND(ISNUMBER(H3),H3>8),H3-8,0)+I3+J3

 

[Farrar35594]

Thank You!

What can I add to that to have the formula had the contents subgtracting 8 hours to get resulting overtime hours? My cells will most likely contain the value "10" as we are working 10-hour days. so, for that day, the employee would have 2 overtime hours.

Thanks Again!

Jeff F.

 

[bryonwoods30]

=IF(AND(ISNUMBER(D3),D3>8),D3-8,0)+IF(AND(ISNUMBER(E3),E3)>8,E3-8,0)+IF(AND(ISNUMBER(F3),F3>8),F3-8,0)+IF(AND(ISNUMBER(G3),G3>8),G3-8,0)+IF(AND(ISNUMBER(H3),H3>8),H3-8,0)+I3+J3


that will take care of everything you need

 

[Farrar35594]

Thank yo very much for your time. I had searched quite a bit for pre-designed sheets but had found nothing close.

I have only been using Excel for a short time but the capabilities are amazing! I also maintain, as part of my Ind. Eng. / Labor and Motion Studies, a record of everything we manufacture along with a yearly forecast, daily/weekly/monthly averages, etc. In the past, this too was done with pencil and paper.

Thanks Again! Your time is appreciated. I understand how important everyone's time is; it's a precious commodity. I need 16 hours a day on my current job which is one reason for my request to move back into our shop.

Thanks!

J. Farrar

 

[Farrar35594]

I spoke too soon.....

That formula returns an error message of #VALUE! until I remove the VAC from the cell.

Is there a better way to do this using a range function as mentioned in an earlier reply?

Thank You,

Jeff

 

[bryonwoods30]

i just tried the formula and it only account for cells that have a number in them and not text, if you copy and paste that formula into the cell you want the result it should work fine because I just did it and there wasn't an error with any text I put in any of the cells.

 

[Toonies]

Hi try this it may help

=IF(AND(OR(ISNUMBER(D3)),D3>8),D3-8,0)+IF(AND(OR(ISNUMBER(E3)),E3>8),E3-8,0)+IF(AND(OR(ISNUMBER(F3)),F3>8),F3-8,0)+IF(AND(OR(ISNUMBER(G3)),G3>8),G3-8,0)+IF(AND(OR(ISNUMBER(H3)),H3>8),H3-8,0)+IF(AND(OR(ISNUMBER(I3)),I3),I3,0)+IF(AND(OR(ISNUMBER(J3)),J3),J3,0)

 

[Farrar35594]

Thank YOu! I just tried the formula and it works well. I also stumbled-bumbled and created this one which seems to work also:

=IF((AND(D4<>"VAC",D4>8)),D4-8,0)+IF((AND(E4<>"VAC",E4>8)),E4-8,0)+IF((AND(F4<>"VAC",F4>8)),F4-8,0)+IF((AND(G4<>"VAC",G4>8)),G4-8,0)+IF((AND(H4<>"VAC",H4>8)),H4-8,0)+I4+J4

Thanks Again!

Jeff F.