I have been fiddling with this equation for a while now and can't figure out where the mistake is.
Can anyone help?
I am using Excel in Denmark and it requires that I use commas instead of periods as a decimal. Don't ask me why, that's just how their number system works.
edit: We are calculating hours worked. For example if the start time at 06:00 and finish at 17:00 with a 0.5 hour break, the hours I bill my client will be 10.5. BUT everything worked over 7.5 hours is charged extra.
- First 7.5 hours is 203.82kr
- First 2 hours after the 7.5 hours is 253.82kr
- Every hour after those 2 hours are 303.85kr
I need the cell with the above mentioned formula to calculate the hours worked based on the times entered in J5 and I5 then subtract the break entered in K5. If the result is over 7.5 hours then it should only show the hours over 7.5 (worked 10.5 hours but only says 2). The extra hour should be calculated in a different cell but will pretty much be the same formula.
I hope this makes sense.
Here is a screenshot of the spreadsheet I am using.
So let me start with the bullet points. I just hope I understand your questions properly.
- I multiplied by 24 so I can subtract the start time from the end time to give me how many hours worked. This way, I simply have to enter what time they clocked in/out and the spreadsheet will figure out the rest.
- No. No one ever works over night.
- Yes. K5 and L5 are the hours actually worked rather than the times they worked.
- I want L5 to display how many hours they worked up to 7.5. M5 to display how many hours over 7.5 hours with a max of 2 hours, and N5 to show how many hours they worked over the 2 hours in M5.
In a nutshell the results should look like this:
- I5 being clock in time
- J5 being clock out time
- K5 being break in hours
- L5 being total hours worked (max 7.5 hours; if worked less than 7.5 hours - ex. 6 hours, then it should say 6
- M5 being hour many hours worked over 7.5 (if they worked more than 7.5 hours but max 2)
- N5 being all remaining hours worked over the 2 hours past 7.5 hours