Click here for important information about Computing.net.

Solved Week number in Excel

January 11, 2021 at 10:35:31
Specs: Windows 8
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.


See More: Week number in Excel


#1
January 11, 2021 at 12:06:54
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 2020

I'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 to 01/28/20 I get -48

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
January 11, 2021 at 12:15:08
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

http://www.skeptic.com/


Reply ↓  Report •

#3
January 11, 2021 at 19:05:24
✔ Best Answer
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


Reply ↓  Report •

Related Solutions

#4
January 12, 2021 at 09:27:13
This formula worked! Thank you.

Reply ↓  Report •

Ask Question