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