Weeknum Formula Problem

**morerockin** · 02-06-2016, 01:31 PM

Hello All

Our week starts on Monday and ends on Sunday. I use the weeknum formula and add "-1" to make sure the week number reflects the start on a Monday.

However, Week 53 of the year jacks everything up! I need my work week to start on Monday and end on Sunday AND I need for Jan 1st 2016 to be Week #1 and NOT Week #53.

See attached. Book2.xlsx

Column B accurately reflects my formula for making sure that it knows my week starts on Monday. However, I don't know what to do in order to get the 3rd row lined out to reflect January 1 as Week # 1.

**AlKey** · 02-06-2016, 01:37 PM

Try this

Formula:

=WEEKNUM(C2,2)

It should return 1 when in B3

**Ace_XL** · 02-06-2016, 01:38 PM

A workaround..

=MIN(WEEKNUM(C2-1),WEEKNUM(C2))

or simply

=WEEKNUM(C2,2)

**bebo021999** · 02-06-2016, 01:39 PM

How about

=WEEKNUM(C2,2)

??

**morerockin** · 02-06-2016, 02:05 PM

All,

I tremendously apologize. I am wrong.

See this picture:

Microsoft Outlook.png

We use the week number per MS Outlook. So I am wrong. I actually need Wk 53 to encompass the full week.

See attached Book2.xlsx

The orange column is what Excel is currently returning. The red column is what I need.

I am adding reputation to all of you now. SORRY!!