If you have trouble with English, you may do better using a newsgroup in
your own language.

You said:
" What seems to be happening ref to the totalling is that when it gets
to
over the 24Hrs total it the carries on to count like a clock would.
eg a total that should read 24hrs:30min in showing the total being
00:30"

but I had previously said:
" Thirdly, in another reply to Jacob's message you said "i had to set
the
cell
format to "Time""
What was wrong with Jacob's suggestion of:
Custom format C1 to [h]:mm ?
You will see that his suggestion copes with values beyond 24 hours
when
you
add them up, but yours doesn't."

Your reply about the sum function doesn't answer the question as to why you
are using =SUM(C1+F1+I1) instead of =C1+F1+I1
The answer is the same, but you've just added unnecessary complication.

If you don't read and address the points that are made to you, neither I nor
Jacob nor any other contributor will be able to help you.
--
David Biddulph

"gadgetman" wrote in message
...
Hi David

The Sum function was to enable the totals hours worked per day for each
member of staff per week to be totalled up which is striaght forward
eg Monday 10:00 to 22:00 = 11.30 hrs worked
Tuesday 08:00 to 13:00 = 5:00 hrs worked
wednesday 10:00 to 14:00 = 6:00hrs worked
and so no ending up at the end of the row with a total equalling in this
case 22:30 worked.
So my wife would entre the start time on say Monday (A1)(10:00) the end
time(A2) (say 14:00) and this would give a total in the next columb (A3)

But the issue start when i had to put in thsi IF function as if they
worked
over 7 hours then a 0.5 hour would have to be deducted from that shift.
which
was working great from the formula which Jacob sent to me, the issue is
when
you total it up as i have explained prior. any suggestions please

There are about 20 staff members and 7 day per week

the total have to be shown as they have to work differnet contractual
hours
some 16hr per weekand other might do 30 hours per week.


"David Biddulph" wrote:

We can't assist you if you don't read what you're told. Please read my
message again.
--
David Biddulph

"gadgetman" wrote in message
...
Hi David
please read my last respsonce to Jacob,

what i am trying to do is set out a spread sheet that enables my wife
to
enter a start time, an end time, with the total hours against each day
and
then to add up the total hours for that week at the end of each row for
her
staff. but if they work over 7 hours per shift then 0.5 hr will have to
be
deducted
eg 10:00 start time and 22:00 end time = total of 11hrs .30mins
if they worked 08:00 to 12:00 then it would be a straight 4:00hrs as
there
is no deduction.

What seems to be happening ref to the totalling is that when it gets
to
over the 24Hrs total it the carries on to count like a clock would.
eg a total that should read 24hrs:30min in showing the total being
00:30


please any asstance would be grateful

"David Biddulph" wrote:

Firstly, could you please explain to us what you want the SUM function
to
do
in =SUM(C1+F1+I1) ?
Why not just =C1+F1+I1 ? What do you want to sum with C1+F1+I1 ?

Secondly, you say "does not come up with this total" but you haven't
told
us
what total you have come up with, so it's rather difficult for us to
tell
what you've done wrong.

Thirdly, in another reply to Jacob's message you said "i had to set
the
cell
format to "Time""
What was wrong with Jacob's suggestion of:
Custom format C1 to [h]:mm ?
You will see that his suggestion copes with values beyond 24 hours
when
you
add them up, but yours doesn't.
--
David Biddulph

"gadgetman" wrote in message
...
Hi again its me sorry

That works great, but when i am trying to add all the totals
together
at
the
end coulb say "J" it does not work as it comes up with just adding
the
time,
in stead of
10:00hrs to 22:00hrs equals 11:30 for Monday (less the .5 hr), for
Tuesday
it is 08:00hrs to 12:00hrs equals 4.0hrs (do not subtract the 0.5
hr
as
it
is less than 7 hrs total), Wednesday 08:00hrs to 12:00hrs equals
4.0hrs
there for the total for the week should read in say columb "J" total
19.5
hrs
for that week
formula used =SUM(C1+F1+I1) but it does not come up with this total?

sorry to be a pain

"Jacob Skaria" wrote:

Entering 10:00 to A1 will automatically custom format cell A1 to
h:mm
Entering 22:00 to B1 will automatically custom format cell B1 to
h:mm

Custom format C1 to [h]:mm and try the below formula

=IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1)

If this post helps click Yes
---------------
Jacob Skaria


"gadgetman" wrote:

Hi Again
Just had a look over the formula you have suggested to try, if
there
an
issues with the "07:00" value as this is a time value and not a
total
between
the times

eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs
threrefore 07:00 is a time value and would generate a zero??

"Jacob Skaria" wrote:

A1 = 10:00
B1 = 22:00

In C1 try the below formula

=IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1)

If this post helps click Yes
---------------
Jacob Skaria


"gadgetman" wrote:

Hi I an trying to generate a formula to enable my wife to
calculate
the off
duty for her section.
i have used 3 cells to start off with just to see if i could
get
it
to work,
cell 1 = start time, Cell 2 = end time, Cell 3 = total.
e.g. start time =08.00, end time =13:00, then total time =
5hrs,
formula say
=sum(d3-d2) simple. Ok
Well this is the part were i have got stuck. If the total is
equal
or more
than 7 hours then i need to subtract 0.5hr, but only if it is
equal
or
greater
so say start time is 10:00, end time is 22:00 then is should
=
11.5
and not
12 hrs as the sum is greater than 7hrs
Can some kind person out there help with the formula

cheers