Add Cells based on conditions and number

Hi,

Its been a while,

Can I please get some help with attached file.

I wound like to add together worked hrs for employees.

conditions
Monday-Friday 8 hrs normal time between 8-10hrs time half and double there after
Saturday 1st 4hrs time half rest double
all night work paid time half, yes no box- box will also be blank for no

results to show on corrosponding line.

thanks

Please explain this again:

all night work paid time half, yes no box- box will also be blank for no


I do not know what you mean!!

This answer removed - doesn't work

I think that this is it. None of the formulae multiply 1.5 time by 1.5, or double by 2....

K3: =IF(B2<>"",SUM(IF($C4:$I4="Yes",0,IF(C3:I3>8,8,C3:I3))),"")

L3: =IF($B2<>"",SUM(IF($C4:$I4="Yes",$C3:$I3,IF($C3:$I3>10,2,IF($C3:$I3>8,$C3:$I3-8)))),"")

M3: =IF($B2<>"",SUM(IF($C4:$I4="Yes",0,IF($C3:$I3>10,$C3:$I3-10))),"")

all are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Thanks guys,

davsth, mostly there though, I don't think cheeking night work.

with the yes no cell, if cell is blank or no. process as a day shift. 8hrs normal 2hrs 1.5 rest 2 and weekend Saturday 1st 4 1.5X and rest 2x.
(note) all sunday day is double time.

if cell is yes the employee has worked a night shift, all night shift is paid 1.5x normal rate. this does include weekend nights

Ahhh I now know why I loved this forum.

glenn,, thanks near bang on. just not calculating weekend day rates correctly.

thanks guys, I am real poor at formulas.

Oh **** !! I never noticed that bit....

K3:
=IF($B2<>"",SUM(IF($C4:$I4="Yes",0,IF($C$1:$I$1="Saturday",0,IF($C$1:$I$1="Sunday",0,IF($C3:$I3>8,8,$C3:$I3))))),"")

L3: =IF($B2<>"",SUM(IF($C4:$I4="Yes",0,IF($C$1:$I$1="Saturday",IF($C3:$I3>4,4,$C3:$I3),IF($C$1:$I$1="Sunday",IF($C3:$I3>4,4,$C3:$I3),IF($C3:$I3>10,2,IF($C3:$I3>8,$C3:$I3-8)))))),"")

M3:
=IF($B2<>"",SUM(IF($C$1:$I$1="Saturday",IF($C3:$I3>4,$C3:$I3-4),IF($C$1:$I$1="Sunday",IF($C3:$I3>4,$C3:$I3-4),IF($C4:$I4="Yes",0,IF($C3:$I3>10,$C3:$I3-10))))),"")

Still not right. I just noticed your change in requirement (maybe clarification of requirement is less grumpy-sounding) about sundays.

One more go. if this is incorrect, for the sheet attached HERE, tell me WHICH answers are incorrect, and what each incorrect answer should be.

Sorry didn't mean to come off grumpy, though who doesn't get grumpy working sundays (lol)
I see I added that late, sorry about that.

everything else looks, sweet

It was me that was getting grumpy... having to have another go at it 'cos i didn't read it correctly first (or second...) time round. Anyhow...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I think we need another if yes/no in there. as its not working for night shift at time half.

Post a sheet showing what you mean.

Forget it. It was obvious....

Whoooottt,

Thanks for the time and help from everyone involved. gleen cheers for sticking it through.

A bit of a marathon... but we got there!!!