Hours Analysis Per Week

[david gabra]

Hi

Let me start, if you figure this out I will be so so happy. I have no idea and stumped. Starts easy...

1.
All employees who worked for more that 48 hours a week. If they did, I need a message to state - 'Yes'. I guess we need a sum on the column called 'Hours' per Week. - that the easy part.


2.
I need to know the total number of weeks where the hours worked was above 48.
bit harder.


3. Now the really tricky bit I believe.

I need to the count of number of CONSECUTIVE WEEKS ONLY where where a person has exceeded the 48 hour limit for- this I have no clue how to do.

I have attached a work example, where I need the logic.

Please keep the solution as simple as possible.

Many thanks


Thank you


David.

 

[Hui]

E3: =COUNTIFS($A$3:$A$35,A3,$C$3:$C$35,">"&48)
copy down

F3: =IF(AND(A3=A2,C3>48),F2+1,IF(C3>48,1,0))
copy down

The formulas assume Greater Than 48, if you want to include 48 change the three > to >=

 

[david gabra]

Hi

The results need to be per employee in column A

I tried that it does not give the desired result.
The weeks are column B

So the logic needs to see if the weeks are consecutive ((column B) and also greater that 48 - for column F

we need to take into account also the week column.

so we need the fomuale to stop an 2018/24 -since even though 2018/34 is greater that 48 - it is NOT consecutive.
222 2018/23 55
222 2018/24 55
222 2018/34 55


Please recheck to get my desired results.

Can you attach the excel with the fomuale in it, since maybe I am doing something wrong on my side.

dave

 

[Hui]

Change F3 to:
=IFERROR(IF(AND(A3=A2,C3>48,VALUE(RIGHT(B3,2))=(RIGHT(B2,2)+1)),F2+1,IF(C3>48,1,0)),IF(B2="WEEK",1,0))
copy down

Please highlight what is wrong :

 

[david gabra]

You are amazing!!!!!!

 

[david gabra]

Hi

F3: =IF(AND(A3=A2,C3>48),F2+1,IF(C3>48,1,0)) - this works

=IFERROR(IF(AND(A3=A2,C3>48,VALUE(RIGHT(B3,2))=(RIGHT(B2,2)+1)),F2+1,IF(C3>48,1,0)),IF(B2="WEEK",1,0)) - this does not

why?

It works in the example above but not in my real excel.

 

[Hui]

David

Probably because you have calculation set to manual
Set it to Automatic and I get :

 

[david gabra]

I am embarrased

 

[Hui]

Don't worry it had me going for a minute...