formula help

[Andy Bolger]

My existing formula in F5 (total time)
=IF(OR(D5="AL",D5="SICK",D5="PH"),8,IF(D5="","0",(((E5-D5)+(E5<D5))*24)-0.5))

A5 = day of the week (monday etc)
B5 = date
D5 = start time (military time)
E5 = finish time (military time)
G5 = total overtime
H5 = time and a half
I5 = double time

My problem is:
on a weekend i have to pay a minimum of 3 hours so i need the total hours,
total overtime and time and a half column to show this.

many thanks
andy

 

[Claus Busch]

Hallo Andy,

Am Sun, 1 May 2011 15:08:01 +1000 schrieb Andy Bolger:

My existing formula in F5 (total time)
=IF(OR(D5="AL",D5="SICK",D5="PH"),8,IF(D5="","0",(((E5-D5)+(E5<D5))*24)-0.5))

My problem is:
on a weekend i have to pay a minimum of 3 hours so i need the total hours,
total overtime and time and a half column to show this.

=IF(OR(D5={"AL","SICK","PH"}),8,MAX((WEEKDAY(B5,2)>5)*3,MOD(E5-D5,1)*24-0.5))


Regards
Claus Busch

 

[Andy Bolger]

Thanks Claus,
formula looks great except for one thing, it puts 3 hours in even if they
haven't worked.

"Claus Busch" wrote in message
Hallo Andy,

Am Sun, 1 May 2011 15:08:01 +1000 schrieb Andy Bolger:

My existing formula in F5 (total time)
=IF(OR(D5="AL",D5="SICK",D5="PH"),8,IF(D5="","0",(((E5-D5)+(E5<D5))*24)-0.5))

My problem is:
on a weekend i have to pay a minimum of 3 hours so i need the total hours,
total overtime and time and a half column to show this.

=IF(OR(D5={"AL","SICK","PH"}),8,MAX((WEEKDAY(B5,2)>5)*3,MOD(E5-D5,1)*24-0.5))


Regards
Claus Busch

 

[Claus Busch]

Hallo Andy,

Am Tue, 3 May 2011 21:41:51 +1000 schrieb Andy Bolger:

formula looks great except for one thing, it puts 3 hours in even if they
haven't worked.

try this:
=IF(D5="",0,IF(OR(D5={"AL","SICK","PH"}),8,MAX((WEEKDAY(B5,2)>5)*3,MOD(E5-D5,1)*24-0.5)))


Regards
Claus Busch