Excel formula help

[All that jazz]

Trying to find a better way of setting up my weekly timesheet calculation in Excel and am half way there but struggling to figure out the rest.

I'm using this online template as a guide ("basic with daily totals tab" on the sheet).

I fully understand the 'IF' formulas in cells F8 and F9 but I don't work out my breaks like that. I have to deduct a 45 min break each shift (no set times) but I also have a 'minimum 8 hours' clause in place which overrides that, ie. if I only work 1 hour then the client gets charged for 8 hours. If I worked 10 hrs then my invoice would be for 9.25 hours because a 45 min break would need to be deducted, but if I only worked 8.25 hours then only 15 mins of break would be deducted because the 8 hr "rule" takes precedence there.

How can I implement that into the formula so that the next columns for calculating the monetary amounts for standard time and overtime show the correct figures?

At the moment I input the times and deduct the breaks manually where necessary but I'd like to just enter my start and finish times and the sheet calculate everything for me.

Thanks .

[AW10]

How about this:

blank out column C (delete when done; just leaving it place for the moment to not confuse columns)
replace column D with the length of break that you actually physically take in the format of hh:mm
insert a column after E that is the value in column E minus the value in column B minus the value in column D and title it actual hours worked
in column G put an IF statement as follows =IF(F8>0,8,0) - in other words if you work any hours at all your regular hours are 8
in column H put an IF statement as follows =IF(G8=8,F8-8),0) - any hours you work more than 8 are overtime hours

[davepoth]

With time worked in A1, it would probably be something like this:

=if(A1<8.75,8,A1-0.75)

that doesn't work with your start time and end time, but that will be a bit different depending on how it's formatted (as proper times, or are you just saying that 8:30am is 8.5 and 5:30pm is 17.5, for example?)

[All that jazz]

Thanks chaps. I actually figured it out after doing some research. I got this to work (where G2 is total time on site and the 20 and the 30 are the rates) :

=IF(G2>0,8/24,0)*20*24+IF(G2>TIME(8,45,0),G2-TIME(8,45,0),0)*30*24