Additional hours

  • Thread starter Thread starter sonar
  • Start date Start date
S

sonar

Hi

Thanks for the tip.

Is there a way that I can use =Max(formula,0) with an if statement? M
formula to workout additional hours are as follows:

=IF(((HOUR(J14)*60+MINUTE(J14)-HOUR(K14)*60-MINUTE(K14))/60+(HOUR(Q14)*60+MINUTE(Q14)-HOUR(R14)*60-MINUTE(R14))/60+(HOUR(X14)*60+MINUTE(X14)-HOUR(Y14)*60-MINUTE(Y14))/60+(HOUR(AE14)*60+MINUTE(AE14)-HOUR(AF14)*60-MINUTE(AF14))/60+(HOUR(AL14)*60+MINUTE(AL14)-HOUR(AM14)*60-MINUTE(AM14))/60+(HOUR(AS14)*60+MINUTE(AS14)-HOUR(AT14)*60-MINUTE(AT14))/60+(HOUR(AZ14)*60+MINUTE(AZ14)-HOUR(BA14)*60-MINUTE(BA14))/60)<BG14,((HOUR(J14)*60+MINUTE(J14)-HOUR(K14)*60-MINUTE(K14))/60+(HOUR(Q14)*60+MINUTE(Q14)-HOUR(R14)*60-MINUTE(R14))/60+(HOUR(X14)*60+MINUTE(X14)-HOUR(Y14)*60-MINUTE(Y14))/60+(HOUR(AE14)*60+MINUTE(AE14)-HOUR(AF14)*60-MINUTE(AF14))/60+(HOUR(AL14)*60+MINUTE(AL14)-HOUR(AM14)*60-MINUTE(AM14))/60+(HOUR(AS14)*60+MINUTE(AS14)-HOUR(AT14)*60-MINUTE(AT14))/60+(HOUR(AZ14)*60+MINUTE(AZ14)-HOUR(BA14)*60-MINUTE(BA14))/60)-E14,"12:00")

I know, I know, this is long...........

My problem is that I am siting with a staff register where I have t
split the Total times as follows:

to brief:
12hrs per day = normal time
12hrs > per day = overtime

28hrs per week = normal time
28hrs > and <40hrs per week = additional hours

40hrs> per week = overtime

The formula works, but only if the person has worked a base of 28hr
for the week, if its less, it gives me a negative figure.

hence the reason I would like to get rid of the negative figures.

can anyone help
 
sonar > said:
=IF(((HOUR(J14)*60+MINUTE(J14)-HOUR(K14)*60-MINUTE(K14))/60
+(HOUR(Q14)*60+MINUTE(Q14)-HOUR(R14)*60-MINUTE(R14))/60
+(HOUR(X14)*60+MINUTE(X14)-HOUR(Y14)*60-MINUTE(Y14))/60
+(HOUR(AE14)*60+MINUTE(AE14)-HOUR(AF14)*60-MINUTE(AF14))/60
+(HOUR(AL14)*60+MINUTE(AL14)-HOUR(AM14)*60-MINUTE(AM14))/60
+(HOUR(AS14)*60+MINUTE(AS14)-HOUR(AT14)*60-MINUTE(AT14))/60
+(HOUR(AZ14)*60+MINUTE(AZ14)-HOUR(BA14)*60-MINUTE(BA14))/60)<BG14,
((HOUR(J14)*60+MINUTE(J14)-HOUR(K14)*60-MINUTE(K14))/60
+(HOUR(Q14)*60+MINUTE(Q14)-HOUR(R14)*60-MINUTE(R14))/60
+(HOUR(X14)*60+MINUTE(X14)-HOUR(Y14)*60-MINUTE(Y14))/60
+(HOUR(AE14)*60+MINUTE(AE14)-HOUR(AF14)*60-MINUTE(AF14))/60
+(HOUR(AL14)*60+MINUTE(AL14)-HOUR(AM14)*60-MINUTE(AM14))/60
+(HOUR(AS14)*60+MINUTE(AS14)-HOUR(AT14)*60-MINUTE(AT14))/60
+(HOUR(AZ14)*60+MINUTE(AZ14)-HOUR(BA14)*60-MINUTE(BA14))/60)-E14,
"12:00")
Click to expand...
....

First, do you have periods spanning midnight? If so, and if K14 were 11:23
PM and J14 were 02:46 AM, you'll get a negative value by subtracting the
fractional hours of K14 from J14. I doubt you want that. If these cells
contain day serial numbers as well as times of day, replace

(HOUR(x)*60+MINUTE(x)-HOUR(y)*60-MINUTE(y))/60

with

(INT(x*1440)-INT(y*1440))/60

Next, you seem to be working on every 7th column starting with J/K. So you
could shorten your formula and handle midnight crossovers using

=IF(SUMPRODUCT(INT(N(OFFSET(J14,0,{0,7,14,21,28,35,42},1,1))*1440)
-INT(N(OFFSET(K14,0,{0,7,14,21,28,35,42},1,1))*1440))/60<BG14,
SUMPRODUCT(INT(N(OFFSET(J14,0,{0,7,14,21,28,35,42},1,1))*1440)
-INT(N(OFFSET(K14,0,{0,7,14,21,28,35,42},1,1))*1440))/60-E14,"12:00")
 
Back
Top