Hi again Per,

Emailed you with a request. (Hopefully the email addy in your post works)

For the life of me I can't get his formula to work. Unfortunately I can't
show pics on this newsgroup or I would post a screenshot of the error
message etc.





"Per Jessen" wrote in message
...
Hi

Thanks for your reply.

Is column F formatted as time?

Maybe the syntax is changed in excel 2007. Select the cell with the
formula. In the formula line place the cursor in the statement Time(5...)
and click on the equal sign left to the formula line.

That should help you with the syntax.

Regards,

Per
"R" <none skrev i en meddelelse
...
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 ;-) ?