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

roster
 

"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

roster
 

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

roster
 

"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

roster
 

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