weekend timesheet formula help

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

Andy Bolger

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

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)))
 
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

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

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