Working out a rota....If function or time?

[R]

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 ;-) ?

 

[Per Jessen]

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

 

[Sandy Mann]

Just to add to Per Jessen's excellent post,

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

If the hours are liable to be more than 24 hours, which I assume they will,
format the column as Custom format [h]:mm to prevent the hours rolling over
into day when they reach 24 hours.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[R]

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]

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]

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.