roster

[Greg Brow]

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

 

[PaulD]

: 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]

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

 

[Greg]

My mistake I forgot to use it like an array

Soon as i did that it worked

Thanks for the help

Greg