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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

R - 05 Feb 2008 06:28 GMT

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

Reply to this Message

Per Jessen - 05 Feb 2008 08:12 GMT

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

> 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.
[quoted text clipped - 16 lines]
> Can any kind soul assist please because I am going mad trying to figure it
> out ;-) ?

Reply to this Message

Sandy Mann - 05 Feb 2008 11:19 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi
> All columns is to be formatted as time.
[quoted text clipped - 33 lines]
>> Can any kind soul assist please because I am going mad trying to figure
>> it out ;-) ?

Reply to this Message

R - 05 Feb 2008 19:03 GMT

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 ?

> Hi
> All columns is to be formatted as time.
[quoted text clipped - 33 lines]
>> Can any kind soul assist please because I am going mad trying to figure
>> it out ;-) ?

Reply to this Message

Per Jessen - 05 Feb 2008 20:37 GMT

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

> Thanks for the input Per,
>
[quoted text clipped - 46 lines]
>>> Can any kind soul assist please because I am going mad trying to figure
>>> it out ;-) ?

Reply to this Message

R - 07 Feb 2008 06:58 GMT

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.

> Hi
>
[quoted text clipped - 61 lines]
>>>> Can any kind soul assist please because I am going mad trying to figure
>>>> it out ;-) ?

Reply to this Message