Thanks for the input Per,

I seem to get an error message of incorrect formula
I have done the E column as D minus C with no formatting and it returns the
correct hours and minutes worked as a rota.

Your formula for calculating the break shows an error though where Excel
highlights the number 5 in your formula and notes it as "Time Hour Minute
Second.

Perhaps Excel 2007 calculates differently ?



"Per Jessen" wrote in message
...
Hi
All columns is to be formatted as time.

I would have start time i column C and end time in column D. Calculate the
working hours in column E "=D1-C1".

Then use column F as help column and calculate working hours with or
without break. =IF(E1<TIME(5;0;0); E1; E1-TIME(0;30;0))
You can hide the column if you like.

Now you can calculate weekly hours using the result in column F and
further.

Regards,

Per


"R" <none skrev i en meddelelse
...
Column A is Names, B is H for Holiday (Mental reminder column) I have
column C (Monday) as a time for a person to work. IE: 09.30 - 12.00.

The next column (D) I want to show how many hours that is as in 2.5 etc.
Do I use the Time function in C5 and if so what calculates it in D5 for
example?

There are (As you would expect) the rest of the week similarly shown with
a total at the end.

If a person works less than 5 hours they don't qualify for a 30min break
so I need to show the total in column Q as the weeks hours (Totals of D F
H J L N & P) less the various days breaks if they work under the 5 hours.
(Shown in C E G I K M O)

Occasionally some people work a few days under 5 hours and a few over and
I can't decide what function to use to show it all in the correct cells.

Can any kind soul assist please because I am going mad trying to figure
it out ;-) ?