Click here for important information about Computing.net.

I have a date in A2 and my formula in B2 is: =ISOWEEKNUM(A2)-ISOWEEKNUM(EOMONTH(A2, -1)-1)

This provides me the week number. For example, my last entry in A2 was 12/28/20. B2 provided me "5" as the 5th week. Today, when I entered 1/4/21 in A3, B3 provided me "-52". The formula has been working fine for me until I entered 2021 entries. Please help.

I do not have ISOWEEKNUM() function as I am still on Excel 2007 and ISOWEEKNUM() came out in the 2013 version. I believe your problem is with the EOMONTH() function.

Your subtracting 1 so the date now becomes the last month in 2020I'm also not sure your formula will work for all dates,

using the WEEKNUM() function in place of the ISOWEEKNUM()

when I changed the date in cell A4 to01/28/20I get-48MIKE

message edited by mmcconaghy

You can try this formula and see if it works for you: =INT((A2-DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3)+WEEKDAY(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3))+5)/7)

MIKE

What about this? =WEEKNUM(A2,2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1),2)+1

Change the [return_type] argument to match your first day of the week.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History