Change value to base 6 for time calculation

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Hello all,

I have a wage formula that calculates overtime depending on what hours are entered for the different overtime rates.

A2 - hourly rate
B2 - base hours
C2 - double time starts after

E2 - Hours worked - user entered
F2 - no.of days - user entered
G2 - Daily rate - user entered
H2 - total

This is the formula that I am working with. it works great but I need to change it to work in a time based environment rather than minutes as 100th fractions of an hour.

Code:
Code:
=(G2+(MAX(0,E2-D2)*3+MEDIAN(0,E2-C2,D2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F3

It is currently based on the decimal point system ie to 6.75 is quater to seven.

I was just wondering how I change it so that the Hours E2 works on a base 6 calculation.

ie so that quater past six is written as 6.15 and that the formula can adjust accordingly?

much appreciated, let me know if it needs any further clarification.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
How about replacing references to E2 with INT(E2)+(MOD(E2,1)/0.6)
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
West man I have only one word for you ...



legend

thanks so much

johnny
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,685
Members
410,697
Latest member
srishtijain0708
Top