I have asked this before but I am looking to have a spreadsheet that will
calculate the hours worked minus their meal breaks. I would like to do 2
things:

1st one is :

We work more than 5 hours we need to have a 30 minute meal break, If we
work more than 8 hours we have to have an hour.

Can i make a macro that will calculate this automatically.

part 2:

Is it possible to make excel not show the results unless the work times have
been inserted into cells b3 and c3.

Thanks in advance

Greg

"Greg Brow" wrote in message
...
: I have asked this before but I am looking to have a spreadsheet that will
: calculate the hours worked minus their meal breaks. I would like to do 2
: things:
:
: 1st one is :
:
: We work more than 5 hours we need to have a 30 minute meal break, If we
: work more than 8 hours we have to have an hour.
:
: Can i make a macro that will calculate this automatically.

yes you can use a macro, but how about a formula
assuming this is pasted in cell D6

=IF((C6-B6)*24<5,(C6-B6)*24,IF((C6-B6)*248,(C6-B6)*24-1,(C6-B6)*24-0.5))

:
: part 2:
:
: Is it possible to make excel not show the results unless the work times
have
: been inserted into cells b3 and c3.
:
<snip

yes, add an "isblank" check in the formula
again assuming this is pasted in cell D6

=IF(ISBLANK(C6),"",IF((C6-B6)*24<5,(C6-B6)*24,IF((C6-B6)*248,(C6-B6)*24-1,(
C6-B6)*24-0.5)))

If you then protect the spreadsheet and hide formulas, no one will know what
is going on :)
Of course the trouble I always have with this is users inserting rows. If
you have this trouble them perhaps a macro using the change event is more in
order for you.
Paul D

Sorry it is not working I'll try to explain it better.

I'll write it properly

on a row

a b c d e
tuesday feb 1 08:00 20:30

In column E i would like the actual hours worked minus the meal breaks

We work more than 5 hours we need to have a 30 minute meal break, If we
work more than 8 hours we have to have an hour.

I also want to only have this show when a time is written in the cells

Thanks again

Greg



"PaulD" <nospam wrote in message
...
"Greg Brow" wrote in message
...
: I have asked this before but I am looking to have a spreadsheet that
will
: calculate the hours worked minus their meal breaks. I would like to do
2
: things:
:
: 1st one is :
:
: We work more than 5 hours we need to have a 30 minute meal break, If we
: work more than 8 hours we have to have an hour.
:
: Can i make a macro that will calculate this automatically.

yes you can use a macro, but how about a formula
assuming this is pasted in cell D6

=IF((C6-B6)*24<5,(C6-B6)*24,IF((C6-B6)*248,(C6-B6)*24-1,(C6-B6)*24-0.5))

:
: part 2:
:
: Is it possible to make excel not show the results unless the work times
have
: been inserted into cells b3 and c3.
:
<snip

yes, add an "isblank" check in the formula
again assuming this is pasted in cell D6

=IF(ISBLANK(C6),"",IF((C6-B6)*24<5,(C6-B6)*24,IF((C6-B6)*248,(C6-B6)*24-1,(
C6-B6)*24-0.5)))

If you then protect the spreadsheet and hide formulas, no one will know
what
is going on :)
Of course the trouble I always have with this is users inserting rows. If
you have this trouble them perhaps a macro using the change event is more
in
order for you.
Paul D

"Greg Brow" wrote in message
...
: Sorry it is not working I'll try to explain it better.
:
<snip

Can you explain what is not working with the formula?
I realize I used the wrong cells so you would need to shift the value over
so C is changed to D and B is changed to C
Paul D

My mistake I forgot to use it like an array

Soon as i did that it worked

Thanks for the help

Greg
"PaulD" <nospam wrote in message
...
"Greg Brow" wrote in message
...
: Sorry it is not working I'll try to explain it better.
:
<snip

Can you explain what is not working with the formula?
I realize I used the wrong cells so you would need to shift the value over
so C is changed to D and B is changed to C
Paul D