Timesheet Overtime Calulations - Excel 2002

[Deborah]

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(E6>G6,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!

 

[Sandy Mann]

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

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

 

[BoniM]

Change your formula in G to:

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

That should make everything work... good luck!

 

[BoniM]

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

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

 

[Deborah]

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.

 

[Sandy Mann]

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

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