PDA

View Full Version : Solved: Modify formula to times by 1.5 after midnight



Barryj
08-25-2006, 03:28 AM
I have this formula that at the moment times by 1.5 any time worked over 8 hours.

Can anyone point me in the right direction how I can modify it so the time before midnight is single time and after midnight it is times by 1.5.

I have attached a file showing how this currently works.

OBP
08-25-2006, 07:52 AM
Do you mean that the "overtime" part is both before and after midnight and the part before midnight is at single time and the part after midnight is at 1.5 times?

Bob Phillips
08-25-2006, 07:58 AM
What happens as in the first row where there are more than 8 hours after midnight, would all of those be at 1.5, i.e. 17:49 hours?

OBP
08-25-2006, 08:12 AM
xld, if the values are the total time worked then only the last 4.53 hours would count as "overtime" and be at 1.5 times (I think) :dunno

Barryj
08-25-2006, 05:46 PM
The time before midnight would be normal rate and the time after midnight would be *1.5.

So if the person started at 21:00 and finished at 05:00 then the first 3 hours would be single rate and the 5 hours after midnight be *1.5 for a total of 10.5 hours.

Hope that helps?

Currently what is happening in the file is that any time over 8 hours is times by 1.5
anything under 8 hours is single rate.

So that if a person works 10 hours they would be payed for 11 as hours 9 & 10 are at 1.5.

Bob Phillips
08-26-2006, 02:07 AM
Let me get this striaght,

If they worked:
- 5 hours all after midmight, that is 7.5 hours pay
- 10 hours, 1 after midnight is 10.5
- 12 hours all before midnight is 12?

Barryj
08-26-2006, 05:53 AM
Yes that is correct, before midnight single time, after midnight time and a half.

Shazam
08-26-2006, 09:23 AM
This might work.

Input formula in cell D2 and copied down.


=IF(B2>=TIME(12,0,0),MOD(C2-B2,1)*24,IF(B2+C2,IF(B2<8/24,MIN(C2,8/24)-B2,IF(C2< B2,MIN(C2,8,24),0))*24,0)*1.5)


Hope that helps.

Shazam
08-26-2006, 09:29 AM
Or this might be better.

Input formula in cell D2

=IF(B2>=TIME(12,0,0),MOD(C2-B2,1)*24,0)


Input formula in cell E2

=IF(B2+C2,IF(B2<8/24,MIN(C2,8/24)-B2,IF(C2< B2,MIN(C2,8,24),0))*24,0)*1.5


and then input formula in cell F2

=D2+E2

and copy all the formulas down.

Shazam
08-26-2006, 01:04 PM
After re-reading the thread it?s a little difficult to understand, well as for me anyway dealing with Times.


Please look at the the sample workbook below.

Bob Phillips
08-27-2006, 04:41 AM
Shazam,

I don't see what your difficulty is, you seem to have got the solution correct AFAICS.

The formula could be simplified to

=MOD(C3-B3,1)+(B3>C3)*C3*0.5

as a time result

Barryj
08-27-2006, 06:05 AM
Thanks for the help guys works great, I will mark this as solved.