Formulas for Time Sheets

[czbacnik]

I am creating a time sheet on Excel. I have a colum for time in, time
out, and total hours worked. In the total hours worked column I had to
use the formula: =((C8+12)-B8)-0.5

The -.05 refers to the half hour lunch that is not counted as hours
worked.

What kind of formula do I need in case someone is either sick or there
is a vacation day? When no number (or 0) is in the first two cells, my
total hours worked equals 11.5!

What can I do so that

(a) the total hours worked colum always equals zero if no numbers are
input

(b) lock the formulas so when I send this out, no one can change or
mess it up

I appreciate any help!

Carla

 

[Biff]

Hi!

Why are you adding 12 to C8? Is it because the time spans past midnight? If
so:

C8 = end time = 3:00 AM
B8 = start time = 3:00 PM

=IF(COUNT(B8,C8)<2,0,(C8-B8+(C8<B8))*24-0.5)

If it's for some other reason:

=IF(COUNT(B8,C8)<2,0,((C8+12)-B8)-0.5)

(b) lock the formulas so when I send this out, no one can change or
mess it up

Protect the sheet.

Select the entire sheet by clicking the button that is to the left of column
A and above row 1.
Goto Format>Cells>Protection tab.
Uncheck Locked
OK
Now select the cells with the formulas that you want locked.
Goto Format>Cells>Protection tab.
Select Locked
OK

Now Goto Tools>Protection>Protect Sheet

Depending on what version of Excel you're using there are various options.

Biff

 

[Peo Sjoblom]

1.

=IF(COUNT(B8:C8)<2,0,((C8+12)-B8)-0.5)

2.

select the whole sheet, do format>cells>protection, uncheck locked
while still selected press F5, click special and click formulas, do
format>cells>protection
and check locked, finally do tools>protection and protect the sheet. Now
they can edit everything but the formulas unless they go here and ask about
cracking the protection


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Biff]

I wrote the formula based on B8 and C8 being empty and overlooked the "(or
0)" part!

When no number (or 0) is in the first two cells

So:

=IF(SUM(B8,C8)=0,0,(C8-B8+(C8<B8))*24-0.5)

That will cover both situations.

Biff