SOLVED

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

Copper Contributor

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

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. A B C D E F G H I J K L 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 2019 0 0 18 18 18 18 0 0 0 28 Apr 2019 B 16 Apr 2019 18 0 18 19 18 25 0 0 0 28 Apr 2019 C 16 Apr 2019 18 20 20 18 0 16 18 18 0 16 Apr 2019 D 2 Feb 2019 0 0 18 0 0 18 1 2 3 N/A E 1 Jan 2019 1 2 3 4 0 18 18 18 0 N/A

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

9 Replies

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

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

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

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

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

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

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

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

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

Hi @Brian_Chan1998,

thanks for the update.

I am now on my third strike.

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

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

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

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

``=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.

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

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

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

=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.