WEEKNUM Returning wrong #?

Unfortunately, week numbers aren't quite as simple and straightforward as we'd like. Have a read of this wiki article on the subject:

Week Numbers

Cheers,

Originally Posted by MnMCarta

I would assume that the first week of the year is Jan. 1st but the formula returns #2 starting at Jan 4th. Why is that?

Always good to read Excel help pages first. (Although they often contain some factual errors. :-( ) Click on https://support.office.com/en-us/art...rs=en-US&ad=US.

Specifically:

There are two systems used for this function:

System 1[:] The week containing January 1 is the first week of the year, and is numbered week 1.

System 2[:] The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

Also see an explanation of the optional second parameter ("return type").

Try this in E2:

=INT((A2-DATE(YEAR(A2),1,1))/7)+1

Originally Posted by MnMCarta

I found this though

=TRUNC(((AU2-DATE(YEAR(AU2),1,0))+6)/7)

and it seems to work but might be because the first fell on a sunday. lol Well, at least I have a year until it might not work

You haven't really said what you want the formula to do?

That formula makes the first 7 days of the year week 1, the next 7 week 2 etc. At the end of the year the last day (or two days in a leap year) will be in week 53. It takes no account of days of the week. Is that what you need? If not then please explain