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

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

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


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


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

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

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

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