software

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!

CarlaHi!

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)lt;2,0,(C8-B8 (C8lt;B8))*24-0.5)

If it's for some other reason:

=IF(COUNT(B8,C8)lt;2,0,((C8 12)-B8)-0.5)

gt; (b) lock the formulas so when I send this out, no one can change or
gt; 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 Formatgt;Cellsgt;Protection tab.
Uncheck Locked
OK
Now select the cells with the formulas that you want locked.
Goto Formatgt;Cellsgt;Protection tab.
Select Locked
OK

Now Goto Toolsgt;Protectiongt;Protect Sheet

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

Biff

gt; wrote in message oups.com...
gt;I am creating a time sheet on Excel. I have a colum for time in, time
gt; out, and total hours worked. In the total hours worked column I had to
gt; use the formula: =((C8 12)-B8)-0.5
gt;
gt; The -.05 refers to the half hour lunch that is not counted as hours
gt; worked.
gt;
gt; What kind of formula do I need in case someone is either sick or there
gt; is a vacation day? When no number (or 0) is in the first two cells, my
gt; total hours worked equals 11.5!
gt;
gt; What can I do so that
gt;
gt; (a) the total hours worked colum always equals zero if no numbers are
gt; input
gt;
gt; (b) lock the formulas so when I send this out, no one can change or
gt; mess it up
gt;
gt; I appreciate any help!
gt;
gt; Carla
gt;
1.

=IF(COUNT(B8:C8)lt;2,0,((C8 12)-B8)-0.5)

2.

select the whole sheet, do formatgt;cellsgt;protection, uncheck locked
while still selected press F5, click special and click formulas, do
formatgt;cellsgt;protection
and check locked, finally do toolsgt;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

nwexcelsolutions.com
gt; wrote in message oups.com...
gt;I am creating a time sheet on Excel. I have a colum for time in, time
gt; out, and total hours worked. In the total hours worked column I had to
gt; use the formula: =((C8 12)-B8)-0.5
gt;
gt; The -.05 refers to the half hour lunch that is not counted as hours
gt; worked.
gt;
gt; What kind of formula do I need in case someone is either sick or there
gt; is a vacation day? When no number (or 0) is in the first two cells, my
gt; total hours worked equals 11.5!
gt;
gt; What can I do so that
gt;
gt; (a) the total hours worked colum always equals zero if no numbers are
gt; input
gt;
gt; (b) lock the formulas so when I send this out, no one can change or
gt; mess it up
gt;
gt; I appreciate any help!
gt;
gt; Carla
gt;
I wrote the formula based on B8 and C8 being empty and overlooked the quot;(or
0)quot; part!

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

So:

=IF(SUM(B8,C8)=0,0,(C8-B8 (C8lt;B8))*24-0.5)

That will cover both situations.

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Why are you adding 12 to C8? Is it because the time spans past midnight?
gt; If so:
gt;
gt; C8 = end time = 3:00 AM
gt; B8 = start time = 3:00 PM
gt;
gt; =IF(COUNT(B8,C8)lt;2,0,(C8-B8 (C8lt;B8))*24-0.5)
gt;
gt; If it's for some other reason:
gt;
gt; =IF(COUNT(B8,C8)lt;2,0,((C8 12)-B8)-0.5)
gt;
gt;gt; (b) lock the formulas so when I send this out, no one can change or
gt;gt; mess it up
gt;
gt; Protect the sheet.
gt;
gt; Select the entire sheet by clicking the button that is to the left of
gt; column A and above row 1.
gt; Goto Formatgt;Cellsgt;Protection tab.
gt; Uncheck Locked
gt; OK
gt; Now select the cells with the formulas that you want locked.
gt; Goto Formatgt;Cellsgt;Protection tab.
gt; Select Locked
gt; OK
gt;
gt; Now Goto Toolsgt;Protectiongt;Protect Sheet
gt;
gt; Depending on what version of Excel you're using there are various options.
gt;
gt; Biff
gt;
gt; gt; wrote in message
gt; oups.com...
gt;gt;I am creating a time sheet on Excel. I have a colum for time in, time
gt;gt; out, and total hours worked. In the total hours worked column I had to
gt;gt; use the formula: =((C8 12)-B8)-0.5
gt;gt;
gt;gt; The -.05 refers to the half hour lunch that is not counted as hours
gt;gt; worked.
gt;gt;
gt;gt; What kind of formula do I need in case someone is either sick or there
gt;gt; is a vacation day? When no number (or 0) is in the first two cells, my
gt;gt; total hours worked equals 11.5!
gt;gt;
gt;gt; What can I do so that
gt;gt;
gt;gt; (a) the total hours worked colum always equals zero if no numbers are
gt;gt; input
gt;gt;
gt;gt; (b) lock the formulas so when I send this out, no one can change or
gt;gt; mess it up
gt;gt;
gt;gt; I appreciate any help!
gt;gt;
gt;gt; Carla
gt;gt;
gt;
gt;