calculate time if greater than a given time
 

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

calculate time if greater than a given time
 

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

calculate time if greater than a given time
 

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

calculate time if greater than a given time
 

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

How to use your same formula to calculate...
 

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

How to use your same formula to calculate...
 

Hi Lisa,

Give us a clue as to what formula you refer!

--
Cheers
macropod
[MVP - Microsoft Word]


"Lisa Kerr" wrote in message ...
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