calculate time if greater than a given time

[draculardw]

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]

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

 

[Mike H]

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

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

 

[draculardw]

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?

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

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

 

[Lisa Kerr]

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

 

[macropod]

Hi Lisa,

Give us a clue as to what formula you refer!

--
Cheers
macropod
[MVP - Microsoft Word]


in message news:[email protected]...