Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
weekend timesheet formula help
in D10 i have start time
in E10 i have finish time in F10 i have total hours worked - current formula is =IF(OR(D10="AL",D10="SICK",D10="PH"),8,IF(D10=""," 0",((E10-D10)+(E10<D10))*24)) My issue is on a weekend i have to pay them for 3 hours minimum many thanks andy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
weekend timesheet formula help
On Apr 20, 3:03*am, "Andy Bolger" wrote:
in D10 i have start time in E10 i have finish time in F10 i have total hours worked - current formula is =IF(OR(D10="AL",D10="SICK",D10="PH"),8,IF(D10=""," 0", ((E10-D10)+(E10<D10))*24)) My issue is on a weekend i have to pay them for 3 hours minimum I assume that means if either the start time or finish time is on a weekend day. =IF(D10="",0, IF(OR(D10={"AL","SICK","PH"}),8, MAX(3*OR(WEEKDAY(D10,2)=6,WEEKDAY(E10,2)=6), (E10-D10)*24))) That assumes that D10 and E10 contain the date as well as time, although you might format them to display only the time. You need the dates in order to distinguish weekday and weekend days. If you put the date into two other cells, say B10 and C10, then: =IF(D10="",0, IF(OR(D10={"AL","SICK","PH"}),8, MAX(3*OR(WEEKDAY(B10,2)=6,WEEKDAY(C10,2)=6), (B10+E10-C10-D10)*24))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
weekend timesheet formula help
I have the day of the week in column A10, the date in B10 and the start time
is in military time/s. Overtime is in 3 columns, total overtime in column G10 time and a 1/2 in H10 (this is where any weekend overtime goes) and double time in I10 again, many thanks andy "joeu2004" wrote in message ... On Apr 20, 3:03 am, "Andy Bolger" wrote: in D10 i have start time in E10 i have finish time in F10 i have total hours worked - current formula is =IF(OR(D10="AL",D10="SICK",D10="PH"),8,IF(D10=""," 0", ((E10-D10)+(E10<D10))*24)) My issue is on a weekend i have to pay them for 3 hours minimum I assume that means if either the start time or finish time is on a weekend day. =IF(D10="",0, IF(OR(D10={"AL","SICK","PH"}),8, MAX(3*OR(WEEKDAY(D10,2)=6,WEEKDAY(E10,2)=6), (E10-D10)*24))) That assumes that D10 and E10 contain the date as well as time, although you might format them to display only the time. You need the dates in order to distinguish weekday and weekend days. If you put the date into two other cells, say B10 and C10, then: =IF(D10="",0, IF(OR(D10={"AL","SICK","PH"}),8, MAX(3*OR(WEEKDAY(B10,2)=6,WEEKDAY(C10,2)=6), (B10+E10-C10-D10)*24))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help: Duration Without Weekend Days | Excel Discussion (Misc queries) | |||
Formula to fetch the date of first weekend of a month | Excel Discussion (Misc queries) | |||
Formula to Remove Weekend Days | Excel Discussion (Misc queries) | |||
workdays formula ends on weekend date | Excel Worksheet Functions | |||
formula to identify weekend dates | Excel Worksheet Functions |