SOLVED

Excel Formula Help - searching the consecutive hours among weeks and find the exact target date

Copper Contributor

Hi All,

 

I am desperately looking for a formula to solve the issue as below:

 

1. The Table below shows the working hour of each employee during week 16 - 24 in 2019

 

2. I will need to identify those employees who work for 18 or more hours per week and work for 4 consecutive weeks or above 

 

3. After identifying those employees, we will need to find the first day of the week that can first achiever the 4 consecutive weeks criteria and display it in the Excel sheet. (The date will be either the Sunday of the week or the join date of the employee, whichever is earlier.) You may check my example below (Column L).

 

  Week No. 
ABCDEFGHIJKL
EmployeeJoin Date2019-162019-172019-182019-192019-202019-212019-222019-232019-24Target Date
A1 Jan 2019001818181800028 Apr 2019
B16 Apr 20191801819182500028 Apr 2019
C16 Apr 2019182020180161818016 Apr 2019
D2 Feb 201900180018123N/A
E1 Jan 2019123401818180N/A

 

Hope someone can help to solve this Issue. Thanks a lot!!

 

 

9 Replies

Hi @Brian_Chan1998,

you can try this approach to identify all employees who work for 18 or more hours per week for 4 consecutive weeks (or more) and find their target date:

Identify Consecutive Weeks Criteria:

- In cell L2 (Target Date for Employee A), enter the following array formula (press `Ctrl+Shift+Enter` after typing the formula):

 

=IF(MAX(IF(D2:K2>=18,ROW(D2:K2)-ROW(D2)+1,0))>=4, MIN(B2, DATE(2019,16,1)), "N/A")

 

The formula checks if there are at least 4 consecutive weeks where the hours worked are 18 or more. If this condition is met, it returns the earlier date between the Join Date and the start of week 16, otherwise, it returns "N/A."

Apply / drag the formula down:

- After entering the array formula in cell L2, you can drag it down to apply the same logic to other employees in column L.

 

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

Hi Leon, Thanks a lot for your reply, but I cant show the desired result with your formula. Would you please help to see the reason?

The result shows N/A

Employee Join Date 2019-16 2019-17 2019-18 2019-19 2019-20 2019-21 2019-22 2019-23 2019-24 Target Date
A 1-Jan-19 0 0 18 18 18 18 0 0 0 N/A

Hi @Brian_Chan1998,

thanks for the update.

Let's try a different approach:

1. In cell L2 (Target Date for Employee A), enter the following formula:

 

=IF(SUMPRODUCT((D2:K2>=18)*1)>=4, MIN(B2, DATE(2019,16,1)), "N/A")

 

2. After entering the formula in cell L2, press "Enter."

3. Copy this formula down for other employees in column L.

 


Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

Hi @LeonPavesic, Thanks for the update. However there is a bug that if they are not in consecutive sequence, the formula still shows the target date.

Employee Join Date 2019-16 2019-17 2019-18 2019-19 2019-20 2019-21 2019-22 2019-23 2019-24 Target Date
A 1-Jan-19 18 0 18 18 18 0 0 0 0 1/1/2019

Hi @Brian_Chan1998,

thanks for the update.

I am now on my third strike.

Please try this:


In cell L2 (Target Date for Employee A), enter this formula and press `Ctrl+Shift+Enter` to make it an array formula:

 

=IF(SUM(IF(D2:K2>=18,1,0)*ROW(D2:K2))>=4*SUM(IF(D2:K2>=18,1,0)), MIN(B2, DATE(2019,16,1)), "N/A")

 

After entering this formula in cell L2, drag it down to apply it to other employees in column L.


Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

Hi Leon, Thanks for the reply. It shows exactly like what the first array formula shows - N/A. Would you please further help with it. Many Thanks.

Employee Join Date 2019-16 2019-17 2019-18 2019-19 2019-20 2019-21 2019-22 2019-23 2019-24 Target Date
A 1-Jan-19 18 18 18 18 18 0 0 0 0 N/A

@Brian_Chan1998 

=IF(MAX(FREQUENCY(IF(C3:K3>=18,COLUMN(C3:K3)),IF(C3:K3<18,COLUMN(C3:K3))))>=4,MAX(INDEX($C$1:$K$1,SMALL(IF((C3:K3>=18)*(D3:L3>=18)*(E3:M3>=18)*(F3:N3>=18),COLUMN($A$1:$I$1)),1)),B3), "N/A")

An alternative could be this formula along with a helper range (C1:K1) for the Sundays. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The results are in column O in order to avoid a circular reference.

hours.png

 

 

 

Hi Oliver, Thanks for the reply. May I know if I need to keep track to more weeks (e.g. let say I need to view the period til week 52 of 2023. "SMALL(IF((C3:K3>=18)*(D3:L3>=18)*(E3:M3>=18)*(F3:N3>=18)" this part also need to extend? Thankss
best response confirmed by Brian_Chan1998 (Copper Contributor)
Solution

@Brian_Chan1998 

=IF(MAX(FREQUENCY(IF(C3:AM3>=18,COLUMN(C3:AM3)),
IF(C3:AM3<18,COLUMN(C3:AM3))))>=4,MAX(INDEX($C$1:$AM$1,
SMALL(IF((C3:AM3>=18)*(D3:AN3>=18)*(E3:AO3>=18)*(F3:AP3>=18),
COLUMN($A$1:$AK$1)),1)),B3), "N/A")

 

Hi Brian. You are welcome. The SMALL part of the formula would extend like in above formula. The other parts have to be adapted accordingly.

excel formula.png

1 best response

Accepted Solutions
best response confirmed by Brian_Chan1998 (Copper Contributor)
Solution

@Brian_Chan1998 

=IF(MAX(FREQUENCY(IF(C3:AM3>=18,COLUMN(C3:AM3)),
IF(C3:AM3<18,COLUMN(C3:AM3))))>=4,MAX(INDEX($C$1:$AM$1,
SMALL(IF((C3:AM3>=18)*(D3:AN3>=18)*(E3:AO3>=18)*(F3:AP3>=18),
COLUMN($A$1:$AK$1)),1)),B3), "N/A")

 

Hi Brian. You are welcome. The SMALL part of the formula would extend like in above formula. The other parts have to be adapted accordingly.

excel formula.png

View solution in original post