Overtime Formula Gives Negative Answers
Greetings & Salutations All,
Thanks to you all, I have a timesheet with various formulas that work such as "=SUM(B27+D27+F27+H27+J27)*24" to total my hours for the week. This was a time-saving venture as opposed to writing it out by hand as before. However, one formula doesn't work as well "=SUM(37.5-D28)" to tally my overtime hours over the paid 37.5 hours a week. I thought that entering "37.5" (my regular weekly total) then subtract "-" the sum total of my actual weekly total "D28" would give me the answer I wanted. Except that it gives me negative answers for example "-1.75" when I want a positive value like "1.75" instead. Please help. How do I fix this? I have attached the Excel 2003 file so you can see what I am talking about in case my explanation wasn't clear nor understood.
| Attachment | Size |
|---|---|
| Automatic Weekly Time Sheet (Forum Example).xls | 44 KB |
Overtime Formula
Last year I was working for a company where I needed to calculate my overtime pay. I know that if my overall time was greater than my normal time, the difference was overtime. So I used this formula:
=IF(D28 >37.5, D28-37.5,0)
My overall time was in cell D28. My normal time was 37.5
So if my overall time was greater than my normal time, I got overtime otherwise I got no overtime.
try this
Change cell D28:
SUM(37.5-D28)
To:
=IF(SUM(D28-37.5)<0,0,SUM(D28-37.5))
"IF" formula is to elinminate negative number.
meaning that you did less that 37.5 hours for the week total.