Forum Discussion
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.
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
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/2019Hi 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