Could one of you math wizards help me take a bite out of a math formula I am
attempting to formulate. I am trying it a lot of different ways sometimes
formula errors sometimes not, but still no results in the cell. This has to
do with payroll calculations. Here is what I have so far that works!
cells []
[09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24)

This is what I need help on
If I am working on the clock at 15:00 I get a shift differential for those
hours beyond 15:00 hours until I clock out, so lets say from the above
example. I would be payed for 14 hours at my regular rate then get a diff
from 15:00 - 23:00 which is a difference of 8 hours
I cannot seem to come up with a formula that will allow me in another cell
to calculate the two time differences and anything greater than 15:00 place
in that cell the total hours of 8
any help thanks
Richard

Hi,

To get the result I think you want try this

B10 C10 D10 E10
Start End Basic time Shift allowance
09:00 23:00 14 8

=(B10-A10)*24 In D10

=MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10

Mike




"draculardw" wrote:

> Could one of you math wizards help me take a bite out of a math formula I am
> attempting to formulate. I am trying it a lot of different ways sometimes
> formula errors sometimes not, but still no results in the cell. This has to
> do with payroll calculations. Here is what I have so far that works!
> cells []
> [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24)
>
> This is what I need help on
> If I am working on the clock at 15:00 I get a shift differential for those
> hours beyond 15:00 hours until I clock out, so lets say from the above
> example. I would be payed for 14 hours at my regular rate then get a diff
> from 15:00 - 23:00 which is a difference of 8 hours
> I cannot seem to come up with a formula that will allow me in another cell
> to calculate the two time differences and anything greater than 15:00 place
> in that cell the total hours of 8
> any help thanks
> Richard
>
>
>
>

That should of course have been

A10 B10 C10 D10
Start End Basic time Shift allowance
09:00 23:00 14 8


"Mike H"

wrote:

> Hi,
>
> To get the result I think you want try this
>
> B10 C10 D10 E10
> Start End Basic time Shift allowance
> 09:00 23:00 14 8
>
> =(B10-A10)*24 In D10
>
> =MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10
>
> Mike
>
>
>
>
> "draculardw" wrote:
>
> > Could one of you math wizards help me take a bite out of a math formula I am
> > attempting to formulate. I am trying it a lot of different ways sometimes
> > formula errors sometimes not, but still no results in the cell. This has to
> > do with payroll calculations. Here is what I have so far that works!
> > cells []
> > [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24)
> >
> > This is what I need help on
> > If I am working on the clock at 15:00 I get a shift differential for those
> > hours beyond 15:00 hours until I clock out, so lets say from the above
> > example. I would be payed for 14 hours at my regular rate then get a diff
> > from 15:00 - 23:00 which is a difference of 8 hours
> > I cannot seem to come up with a formula that will allow me in another cell
> > to calculate the two time differences and anything greater than 15:00 place
> > in that cell the total hours of 8
> > any help thanks
> > Richard
> >
> >
> >
> >

Mike H, it was so close to working
heres what happened
> Start End Basic time Shift allowance
> 09:00 23:00 14 8
the aboved worked here what happened when I entered a different time
> Start End Basic time Shift allowance
> 16:00 17:00 1 2
shift allowence should have also been 1 not 2 in the above
the formula needs to only calculate at 15:00 hours and beyond if I am
punched in
> Start End Basic time Shift allowance
> 12:00 15:00 3 0
> 12:00 16:00 4 1
> 15:00 17:00 2 2
> 16:00 18:00 2 2
> 13:00 18:00 5 3
Does this help explain it better?

"Mike H" wrote:

> That should of course have been
>
> A10 B10 C10 D10
> Start End Basic time Shift allowance
> 09:00 23:00 14 8
>
>
> "Mike H"
>
> wrote:
>
> > Hi,
> >
> > To get the result I think you want try this
> >
> > B10 C10 D10 E10
> > Start End Basic time Shift allowance
> > 09:00 23:00 14 8
> >
> > =(B10-A10)*24 In D10
> >
> > =MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10
> >
> > Mike
> >
> >
> >
> >
> > "draculardw" wrote:
> >
> > > Could one of you math wizards help me take a bite out of a math formula I am
> > > attempting to formulate. I am trying it a lot of different ways sometimes
> > > formula errors sometimes not, but still no results in the cell. This has to
> > > do with payroll calculations. Here is what I have so far that works!
> > > cells []
> > > [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24)
> > >
> > > This is what I need help on
> > > If I am working on the clock at 15:00 I get a shift differential for those
> > > hours beyond 15:00 hours until I clock out, so lets say from the above
> > > example. I would be payed for 14 hours at my regular rate then get a diff
> > > from 15:00 - 23:00 which is a difference of 8 hours
> > > I cannot seem to come up with a formula that will allow me in another cell
> > > to calculate the two time differences and anything greater than 15:00 place
> > > in that cell the total hours of 8
> > > any help thanks
> > > Richard
> > >
> > >
> > >
> > >

I used your formula to successfully calculate hours worked after 11 PM, how do you use your same formula to calculate hours worked prior to a certain time, in this case prior to 7 am?

Thank you in advance! Lisa

Hi Lisa,

Give us a clue as to what formula you refer!

--
Cheers
macropod
[MVP - Microsoft Word]


"Lisa Kerr" wrote in message news:(E-Mail Removed)...
>I used your formula to successfully calculate hours worked after 11 PM, how do you use your same formula to calculate hours worked
>prior to a certain time, in this case prior to 7 am?
>
> Thank you in advance! Lisa