Adding Up Overtime

[cap'n canuck]

I am less knowledgeable of Excel but I was wondering if there was an easy fix to this problem.

I am fixing our timesheet so that I total the hours spent in the day from the SUM of 20 rows and then want to show how many hours over (or under) 8 hours we are per day. This way we can work out overtime and also how much time we need to make up.

I tried subtracting 8 hours from the total but the days 0,0 hours on them will show as -8 hours.

This would be quite easy if I could have the total hours worked at the bottom (which I do) and subtract 8 hours from that total. This works but the automatic total at the bottom puts in 0,0 on days we have not worked (yet) including the weekends so the total would be only correct once we have worked the entire month. It would be easy if I could get rid of the 0,0 when no data has been entered (for the day) or the overtime will not put a 0 in if the total is 0,0 (and not –8)

If you have any ideas it would be wonderful.
Here is the file if you need to look at it.
www.aots.ca/ftp/MARKexceltime1.xls

[Leith Ross]

Hello cap'n canuck,

I am having trouble downloading your file from the site. Can you zip the file and attach it with you post? The size limit for a file is 100kb.

Thanks,
Leith Ross

[cap'n canuck]

I was having troubles even attaching lol
Here it is.. and thanx for getting back so quick!

[Leith Ross]

Hello cap'n canuck,

I have the workbook downloaded. However, I am a little confused about what the problem is. You have a comment that indicates you need a -1 or +4? Does this having anything to do with your post or just something that I should ignore?

Sincerely,
Leith Ross

[Leith Ross]

Hello capn' canuck,

Add this formula to cell C31

Please Login or Register  to view this content.

and drag the formula to the end of the row. This will subtract 8 hours from the daily total only if the daily total is greater than 8. Otherwise it remains zero.

Sincerely,
Leith Ross

[cap'n canuck]

You can ignore that, I was just trying to show that I would eventually like that row filled with the time worked over and under the 8 hour work day. In most cases it should be a 0 but on the days that we work short days and longer days or overtime on the weekend it will show –2 or 3 showing what we did that day. At the far end of the row I have it adding up to give the end of month total.
I may be confusing things a little bit

[cap'n canuck]

That works!
Now the only problem is that is that is will not track the negative hours. If we work less hours (like 7 hours in a day) I would like the number to come out as a negative number.
I guess less than or greater than 0 but not 0.
Thank you for your help

[Leith Ross]

Hello capn' canuck,

Change the formula to this to generate a negative value for less than 8 hours. But what if you work 8 hours? You can't have a negative zero, and subtracting 8 from 8 leaves zero.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[cap'n canuck]

That is confusing.
Your first formula worked perfect for the overtime. We get all of the hours over 8hours, I guess what I could have is a line under that equation to take account of the any hours less than 8 hours.
Then I can total both rows.
Is that possible?


I created a second row and put
=IF(C29>1, C29-8, C29*(-1))
using your formula and it seems to be working

[Leith Ross]

Hello capn' canuck,

I think you might be confusing yourself here. In payroll accounting, you should never have any negative values. You have standard hours, and overtime hours. Both are tracked separately to determine the total hourly wage.

Sincerely,
Leith Ross

[cap'n canuck]

I think you may be right.
What you gave me was quite helpful.
I will be able to do what I wanted with it.
Thank you so much.