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

[Brian_Chan1998]

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!!

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10 · Accepted 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.