formula help

  • Thread starter Thread starter Andy Bolger
  • Start date Start date
A

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
 
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))
Click to expand...
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.
Click to expand...

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


Regards
Claus Busch
 
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))
Click to expand...
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.
Click to expand...

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


Regards
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.
Click to expand...

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
 
Back
Top