Change value to base 6 for time calculation

[cadence]

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:

=(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.

 

[West Man]

How about replacing references to E2 with INT(E2)+(MOD(E2,1)/0.6)

 

[cadence]

West man I have only one word for you ...



legend

thanks so much

johnny