Forum Discussion

Brian_Chan1998's avatar
Brian_Chan1998
Copper Contributor
Sep 14, 2023
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...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Sep 16, 2023

    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.

Brian_Chan1998's avatar
Brian_Chan1998
Copper Contributor
Sep 15, 2023
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
LeonPavesic's avatar
LeonPavesic
Silver Contributor
Sep 15, 2023

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

  • Brian_Chan1998's avatar
    Brian_Chan1998
    Copper Contributor
    Sep 15, 2023
    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

Resources