Hi,
I need to calculate overtime on the timesheet i'm creating.
My dilemma is this:
Where I work, overtime gets calculated as follows:after 19.00 is 1.5x for the first two hours, then 2x after thatI want to caluclate the 1.5x whether the finish time is 20.00, 21.00 or higher.
So far, I'm not getting the results I wanted.
I've tried the following formula:
=IF(E9>19,SUM(((E9-19)-(E9-21))*1.5),"0")
=IF(AND(19<E9,E9<=21),SUM((E9-19)-(E9-20))*OR((E9-19)-(E9-21))*1.5)
Last edited by MEMEM; 12-03-2010 at 09:14 PM.
This is my timesheet thus far
A time is actually a fraction representing part of a day
HTML Code:
In essence, you need to multiply your time by 24 before subtracting a number from it.
Regards
Last edited by TMS; 12-03-2010 at 07:50 AM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Mememe, will the working start finish, etcc be entered as numbers or as hours?
This will make a difference for the building of formulas
If entered as numbers, the standard overtime can be calculated withentered in G9 ( working on the double overtime)
Please Login or Register to view this content.
Also, no need ot enter formulas like =SUM((E9-K9)-D9-C9). the SUM function is redundant just usePlease Login or Register to view this content.
If and only if the times are enterd as numbers this frmula should calculate double overtime provided work has stopped before the following day at 1900
Please Login or Register to view this content.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks