Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|