Forum Discussion
Solved
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 identi...
=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 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
=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.