formula help
 

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

formula help
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

formula help
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

formula help
 

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((WE EKDAY(B5,2)5)*3,MOD(E5-D5,1)*24-0.5)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2