Need advice on what formulas to use for calculating work times at different
overtime rates, all on the same weekly time sheet

IE: Ordinary time 7hrs. (First hr of O/T X 1.5) subsequent hours X 2 (Mon-Fri)
Saturdays - 5hrs X Ord.Time - 6th hour X 1.5 - subsequent hours X 2
Sundays - All hours X 3
Public Holidays - All hours X 3 ( here we need a PubHol identifier,as
well)

End result should be total payable hours at a base rate

The NETWORKDAYS formula has a provision for identifying public holidays, but
it deals only in whole working days, and you're looking to find out hours, so
that's not going to work here. You will have to use the WEEKDAY function,
which tells you what day of the week a certain date is.
I'd suggest you define a range in which you have all your public holidays.
Then have your formula check against that range first, to see if the date in
question is a public holiday. Without checking on the exact syntax of the
formulas (I'll leave that up to you), the formula would look something like
the below, assuming that your dates are in column A, and the hours served on
that date are in column B.

=IF(OR(ISNA(VLOOKUP(A1,publicholidayrange,1,false) )=FALSE,WEEKDAY(A1)=1),B1*3,IF(WEEKDAY(A1)=7,IF(B1 6,(B1-6)*2+1.5+5,IF(B15,(B1-5)*1.5+5,B1)),IF(B18,(B1-8)*2+1.5+5,IF(B17,(B1-7)*1.5+5,B1)))

You'll have to doublecheck the syntax because I did not write this in excel.
The first "OR" section checks to see whether the date is a sunday or a
public holiday, then applies a 3 multiplier to the hours. The second IF
formula checks to see whether it's a Saturday, and applies multipliers
according to your rules. The third IF formula applies to all other weekdays.

"emmcee" wrote:

Need advice on what formulas to use for calculating work times at different
overtime rates, all on the same weekly time sheet

IE: Ordinary time 7hrs. (First hr of O/T X 1.5) subsequent hours X 2 (Mon-Fri)
Saturdays - 5hrs X Ord.Time - 6th hour X 1.5 - subsequent hours X 2
Sundays - All hours X 3
Public Holidays - All hours X 3 ( here we need a PubHol identifier,as
well)

End result should be total payable hours at a base rate

thanks

can I email u direct with an excel attachment for your review
regards
emmcee


"Eddie O" wrote:

The NETWORKDAYS formula has a provision for identifying public holidays, but
it deals only in whole working days, and you're looking to find out hours, so
that's not going to work here. You will have to use the WEEKDAY function,
which tells you what day of the week a certain date is.
I'd suggest you define a range in which you have all your public holidays.
Then have your formula check against that range first, to see if the date in
question is a public holiday. Without checking on the exact syntax of the
formulas (I'll leave that up to you), the formula would look something like
the below, assuming that your dates are in column A, and the hours served on
that date are in column B.

=IF(OR(ISNA(VLOOKUP(A1,publicholidayrange,1,false) )=FALSE,WEEKDAY(A1)=1),B1*3,IF(WEEKDAY(A1)=7,IF(B1 6,(B1-6)*2+1.5+5,IF(B15,(B1-5)*1.5+5,B1)),IF(B18,(B1-8)*2+1.5+5,IF(B17,(B1-7)*1.5+5,B1)))

You'll have to doublecheck the syntax because I did not write this in excel.
The first "OR" section checks to see whether the date is a sunday or a
public holiday, then applies a 3 multiplier to the hours. The second IF
formula checks to see whether it's a Saturday, and applies multipliers
according to your rules. The third IF formula applies to all other weekdays.

"emmcee" wrote:

Need advice on what formulas to use for calculating work times at different
overtime rates, all on the same weekly time sheet

IE: Ordinary time 7hrs. (First hr of O/T X 1.5) subsequent hours X 2 (Mon-Fri)
Saturdays - 5hrs X Ord.Time - 6th hour X 1.5 - subsequent hours X 2
Sundays - All hours X 3
Public Holidays - All hours X 3 ( here we need a PubHol identifier,as
well)

End result should be total payable hours at a base rate

"emmcee" wrote:

Need advice on what formulas to use for calculating work times at different
overtime rates, all on the same weekly time sheet

IE: Ordinary time 7hrs. (First hr of O/T X 1.5) subsequent hours X 2 (Mon-Fri)
Saturdays - 5hrs X Ord.Time - 6th hour X 1.5 - subsequent hours X 2
Sundays - All hours X 3
Public Holidays - All hours X 3 ( here we need a PubHol identifier,as
well)

End result should be total payable hours at a base rate

Can I email u direct with a sample excel sheet sample of our workings
my contact is :

"Eddie O" wrote:

The NETWORKDAYS formula has a provision for identifying public holidays, but
it deals only in whole working days, and you're looking to find out hours, so
that's not going to work here. You will have to use the WEEKDAY function,
which tells you what day of the week a certain date is.
I'd suggest you define a range in which you have all your public holidays.
Then have your formula check against that range first, to see if the date in
question is a public holiday. Without checking on the exact syntax of the
formulas (I'll leave that up to you), the formula would look something like
the below, assuming that your dates are in column A, and the hours served on
that date are in column B.

=IF(OR(ISNA(VLOOKUP(A1,publicholidayrange,1,false) )=FALSE,WEEKDAY(A1)=1),B1*3,IF(WEEKDAY(A1)=7,IF(B1 6,(B1-6)*2+1.5+5,IF(B15,(B1-5)*1.5+5,B1)),IF(B18,(B1-8)*2+1.5+5,IF(B17,(B1-7)*1.5+5,B1)))

You'll have to doublecheck the syntax because I did not write this in excel.
The first "OR" section checks to see whether the date is a sunday or a
public holiday, then applies a 3 multiplier to the hours. The second IF
formula checks to see whether it's a Saturday, and applies multipliers
according to your rules. The third IF formula applies to all other weekdays.

"emmcee" wrote:

Need advice on what formulas to use for calculating work times at different
overtime rates, all on the same weekly time sheet

IE: Ordinary time 7hrs. (First hr of O/T X 1.5) subsequent hours X 2 (Mon-Fri)
Saturdays - 5hrs X Ord.Time - 6th hour X 1.5 - subsequent hours X 2
Sundays - All hours X 3
Public Holidays - All hours X 3 ( here we need a PubHol identifier,as
well)

End result should be total payable hours at a base rate