roster

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" <[email protected]> wrote in message
news:[email protected]...
: 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)*24>8,(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)*24>8,(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
news:[email protected]...
> "Greg Brow" <[email protected]> wrote in message
> news:[email protected]...
> : 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)*24>8,(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)*24>8,(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" <[email protected]> wrote in message
news:[email protected]...
: 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
news:%[email protected]...
> "Greg Brow" <[email protected]> wrote in message
> news:[email protected]...
> : 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
>
>