WEEKNUM in Array

[madocar]

Hi everyone,

is there a way how to fix this. When I put this formula an error is occured.

Problem is with this part of formula ... WEEKNUM($H$2:$H$5)=WEEKNUM(TODAY() ..

Is there any way how to fix or do I only need helper column which calculate weeknum of those dates?

Look to my excel file please.

Thanks a lot for any help

 

[Chihiro]

WEEKNUM is one of the formula that does not accept range argument. As you stated, helper column is probably the easiest route.

 

[Khalid NGO]

Hi,

Not sure 100%, see if this works:

=IFERROR(INDEX($B$2:$M$5,SMALL(IF(IFERROR(INT(($H$2:$H$5-DATE(YEAR($H$2:$H$5),1,1)-WEEKDAY($H$2:$H$5,2))/7)+2,0)=WEEKNUM(TODAY()),ROW($H$2:$H$5)-1),ROWS($M$12:$M12)),COLUMNS($M$10:M$10)),"")

{array formula}

Regards,

 

[Haseeb A]

Hello Madocar,

Try this in the WEEKNUM place

WEEKNUM(OFFSET(H$3,ROW(H$3:H$5)-ROW(H$3),))

So the entire formula would be,

=IFERROR(INDEX($B:$M,SMALL(IF(WEEKNUM(OFFSET(H$3,ROW(H$3:H$5)-ROW(H$3),))=WEEKNUM(TODAY()),ROW(H$3:H$5)),ROWS($M$12:M12)),COLUMNS($M$10:M$10)),"")