Not sure I can explain this without giving someone the whole time sheet but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should be
3:50. If it is a weekend though, all hours have to go in the overtime column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not if it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it. Also the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!

Make the formula in G:

=IF(B6=0,--"08:00",0)

The -- will chnge the text "08:00" into a number. Yoy will probably need to
reformat the F as time.

It sound like you may have other problems so if you want you can send me a
copy of you sheet, just chnge my address below by replacing the part after
the @ as it says.


--
HTH

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


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


"Deborah" wrote in message
...
Not sure I can explain this without giving someone the whole time sheet
but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should be
3:50. If it is a weekend though, all hours have to go in the overtime
column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not if
it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it. Also
the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!

Change your formula in G to:

=IF(B6="w",TIME(8,0,0),0)

That should make everything work... good luck!

"Deborah" wrote:

Not sure I can explain this without giving someone the whole time sheet but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should be
3:50. If it is a weekend though, all hours have to go in the overtime column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not if it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it. Also the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!

Oops! Sorry... did it backwards...

=IF(B2<"w",TIME(8,0,0),0)
or
=IF(B2="w",0,TIME(8,0,0))

"Deborah" wrote:

Not sure I can explain this without giving someone the whole time sheet but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should be
3:50. If it is a weekend though, all hours have to go in the overtime column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not if it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it. Also the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!

Hmm, I tried pasting all 4 formulas and it made no difference :( Will email
to Sandy Mann and see if he can sort it. Very grateful for your time and
effort though.

"BoniM" wrote:

Oops! Sorry... did it backwards...

=IF(B2<"w",TIME(8,0,0),0)
or
=IF(B2="w",0,TIME(8,0,0))

"Deborah" wrote:

Not sure I can explain this without giving someone the whole time sheet but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should be
3:50. If it is a weekend though, all hours have to go in the overtime column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not if it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it. Also the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!

For the archives, Deborah had applied BoniM's formula correctly but was not
referencing them in the Overtime Column.

--

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


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


"Deborah" wrote in message
...
Hmm, I tried pasting all 4 formulas and it made no difference :( Will
email
to Sandy Mann and see if he can sort it. Very grateful for your time and
effort though.

"BoniM" wrote:

Oops! Sorry... did it backwards...

=IF(B2<"w",TIME(8,0,0),0)
or
=IF(B2="w",0,TIME(8,0,0))

"Deborah" wrote:

Not sure I can explain this without giving someone the whole time sheet
but
I'll give it a go!

A B C D E
F
Date W Start Time Finish Time Total Hours Overtime

22 w or blank 08:00 19:50 =D6-C6
=IF(E6G6,E6-G6,0)

G
Hidden
=IF(B6=0,"08:00",0)


All hours over 8 need to calculate in the overtime column so F should
be
3:50. If it is a weekend though, all hours have to go in the overtime
column
- therefore if a weekend the person needs to type a 'w' in column B.

The problem is if it's a weekend the overtime calculates fine but not
if it
is a weekday. I'm sure it's something to do with my formula in G - the
"08:00" not being calculated but I just can't see a way around it.
Also the
overtime column won't total at the bottom because I have negative
numbers/times.

Any help much appreciated and if anyone wants the timesheet itself I'll
happily email it!