Formulas for Time Sheets
 

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

Formulas for Time Sheets
 

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 FormatCellsProtection tab.
Uncheck Locked
OK
Now select the cells with the formulas that you want locked.
Goto FormatCellsProtection tab.
Select Locked
OK

Now Goto ToolsProtectionProtect Sheet

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

Biff

wrote in message
oups.com...
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

Formulas for Time Sheets
 

1.

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

2.

select the whole sheet, do formatcellsprotection, uncheck locked
while still selected press F5, click special and click formulas, do
formatcellsprotection
and check locked, finally do toolsprotection 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



wrote in message
oups.com...
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

Formulas for Time Sheets
 

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

"Biff" wrote in message
...
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 FormatCellsProtection tab.
Uncheck Locked
OK
Now select the cells with the formulas that you want locked.
Goto FormatCellsProtection tab.
Select Locked
OK

Now Goto ToolsProtectionProtect Sheet

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

Biff

wrote in message
oups.com...
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