I am attempting to create a timesheet where hours worked are entered as
"start time", "finish time" and "break length", therefore 1st cell may be
05:30, 2nd cell 17:00, 3rd cell 00:30 and 4th cell is total time worked ( 11
hours).
I also have a column that states the type of day worked eg. "Standard",
"Saturday", "Sunday", Public Holiday" etc. The day worked determines the
breakup of hours betwen different pay rates. I would like to create a formula
for the following criteria:
(A1) is "Day Type" (A5) is "total Hours Worked" (A6) is "normal
hours" (A7) is "overtime hours", therefore IF
- (A1) is "Saturday" and (A5) is equal to or greater than 2 then 2
- (A1) is "Saturday" and (A5) is less than 2 then (A5)
- (A1) is "Standard" and (A5) is greater than 9.6 then 2
- (A1) is "Standard" and (A5) is between 7.7 and 9.5 then (A5) minus (A6)
- (A1) is "Standard" and (A5) is equal to or less thans 7.6 then (A5)
- (A1) does not equal "Saturday" or "Standard" then 0.

I have tried a combination of IF(AND) and have been unsucessful, any help
would be much appreciated.

I am running Excel 2003.

try this :
A B
C D
Day Type Total Hours Worked Normal Hours
If Function

3 Standard 7 5
Result
4

=IF(AND($A3="Saturday",$B3=2),2,IF(AND($A3="Satur day",$B3<2),$B3,IF(AND($A3="Standard",$B3=9.6),2, IF(AND($A3="Standard",AND($B37.7,$B3<=9.5)),$B3-$C3,IF(AND($A3="Standard",$B3<=7.6),$B3,IF(AND($A3 ="Standard",$A3="Saturday"),0)))))

Hope this fix your problem
Good luck





"Keith in Australia" wrote:

I am attempting to create a timesheet where hours worked are entered as
"start time", "finish time" and "break length", therefore 1st cell may be
05:30, 2nd cell 17:00, 3rd cell 00:30 and 4th cell is total time worked ( 11
hours).
I also have a column that states the type of day worked eg. "Standard",
"Saturday", "Sunday", Public Holiday" etc. The day worked determines the
breakup of hours betwen different pay rates. I would like to create a formula
for the following criteria:
(A1) is "Day Type" (A5) is "total Hours Worked" (A6) is "normal
hours" (A7) is "overtime hours", therefore IF
- (A1) is "Saturday" and (A5) is equal to or greater than 2 then 2
- (A1) is "Saturday" and (A5) is less than 2 then (A5)
- (A1) is "Standard" and (A5) is greater than 9.6 then 2
- (A1) is "Standard" and (A5) is between 7.7 and 9.5 then (A5) minus (A6)
- (A1) is "Standard" and (A5) is equal to or less thans 7.6 then (A5)
- (A1) does not equal "Saturday" or "Standard" then 0.

I have tried a combination of IF(AND) and have been unsucessful, any help
would be much appreciated.

I am running Excel 2003.

Thanks Lando,

I came up with something very similar to your suggestion and it is working
beautifully, thanks again
--
Regards,
Keith in Australia


"Lando" wrote:

try this :
A B
C D
Day Type Total Hours Worked Normal Hours
If Function

3 Standard 7 5
Result
4

=IF(AND($A3="Saturday",$B3=2),2,IF(AND($A3="Satur day",$B3<2),$B3,IF(AND($A3="Standard",$B3=9.6),2, IF(AND($A3="Standard",AND($B37.7,$B3<=9.5)),$B3-$C3,IF(AND($A3="Standard",$B3<=7.6),$B3,IF(AND($A3 ="Standard",$A3="Saturday"),0)))))

Hope this fix your problem
Good luck





"Keith in Australia" wrote:

I am attempting to create a timesheet where hours worked are entered as
"start time", "finish time" and "break length", therefore 1st cell may be
05:30, 2nd cell 17:00, 3rd cell 00:30 and 4th cell is total time worked ( 11
hours).
I also have a column that states the type of day worked eg. "Standard",
"Saturday", "Sunday", Public Holiday" etc. The day worked determines the
breakup of hours betwen different pay rates. I would like to create a formula
for the following criteria:
(A1) is "Day Type" (A5) is "total Hours Worked" (A6) is "normal
hours" (A7) is "overtime hours", therefore IF
- (A1) is "Saturday" and (A5) is equal to or greater than 2 then 2
- (A1) is "Saturday" and (A5) is less than 2 then (A5)
- (A1) is "Standard" and (A5) is greater than 9.6 then 2
- (A1) is "Standard" and (A5) is between 7.7 and 9.5 then (A5) minus (A6)
- (A1) is "Standard" and (A5) is equal to or less thans 7.6 then (A5)
- (A1) does not equal "Saturday" or "Standard" then 0.

I have tried a combination of IF(AND) and have been unsucessful, any help
would be much appreciated.

I am running Excel 2003.

"Lando" wrote:

try this :
A B C D
Day Type Ttl Hours Worked Normal Hours If Function

3 Standard 7 5 Result
4

=IF(AND($A3="Saturday",$B3=2),2,IF(AND($A3="Satur day",$B3<2),$B3,IF(AND($A3="Standard",$B3=9.6),2, IF(AND($A3="Standard",AND($B37.7,$B3<=9.5)),$B3-$C3,IF(AND($A3="Standard",$B3<=7.6),$B3,IF(AND($A3 ="Standard",$A3="Saturday"),0,0)))))

Hope this fix your problem
Good luck





"Keith in Australia" wrote:

I am attempting to create a timesheet where hours worked are entered as
"start time", "finish time" and "break length", therefore 1st cell may be
05:30, 2nd cell 17:00, 3rd cell 00:30 and 4th cell is total time worked ( 11
hours).
I also have a column that states the type of day worked eg. "Standard",
"Saturday", "Sunday", Public Holiday" etc. The day worked determines the
breakup of hours betwen different pay rates. I would like to create a formula
for the following criteria:
(A1) is "Day Type" (A5) is "total Hours Worked" (A6) is "normal
hours" (A7) is "overtime hours", therefore IF
- (A1) is "Saturday" and (A5) is equal to or greater than 2 then 2
- (A1) is "Saturday" and (A5) is less than 2 then (A5)
- (A1) is "Standard" and (A5) is greater than 9.6 then 2
- (A1) is "Standard" and (A5) is between 7.7 and 9.5 then (A5) minus (A6)
- (A1) is "Standard" and (A5) is equal to or less thans 7.6 then (A5)
- (A1) does not equal "Saturday" or "Standard" then 0.

I have tried a combination of IF(AND) and have been unsucessful, any help
would be much appreciated.

I am running Excel 2003.