Week # of Month, with Monday beginning Sunday

[JPARKHURST]

I have the following formula which is giving me 5/6/18 as Week 2.

I'm sure I'm making a simple mistake - but why? This same formula gives me dates 4/29 - 5/5 as being in Week 5 of April

Then for 5/6/2018 - 5/12/18 it tells me Week 2.


="Week " & WEEKNUM(N5,1)-WEEKNUM(DATE(YEAR(N5),MONTH(N5),1),1)+1

Any insight is appreciated.

 

[Gerald Higgins]

I haven't used the WEEKNUM function much myself, but taking a look at Excel help for this function, it appears to me that WEEKNUM isn't necessarily appropriate for calculating week number within a month, it's more for week number within a year.

Have you considered a different approach - setting up your own calendar somewhere in the worksheet, and then looking up any date against that calendar ?
You can then set the "week in month" numbers however you like.

By the way, just a small thing - you've quoted dates in at least 3 different formats in your OP, and in addition we have to guess that you're using a MM/DD/YY format

 

[JPARKHURST]

Thanks for the reply.

No, it is not meant for this function really at all, but it seems to be working in all but this one instance.

I guess I could setup a calendar and just manually enter what week they are, but that doesn't seem like an ideal. I'm really hoping I can figure this out programatically vs. just making (and remaking) a data matrix.


Date formats do not reflect the format displayed, just my scattered brain.

 

[Gerald Higgins]

It should be do-able programatically as well, i.e. via a formula, if you can be clear about how you define how you want to calculate the week numbers.

Without having thought about it much, I think the issue comes down to how do you define where exactly week 1 begins.

You've said your week begins on a Monday, so . . .

If the first of the month is a Monday, then week 1 starts on the 1st and goes up to Sunday 7th.
If the first of the month is a Tuesday, what do you want to do about that ? Does Tuesday 1st belong to the last week of the previous month, and week 1 of this month starts on Mon 7th ?
OR, does week 1 start on the last day (Monday) of the previous month ?
Same question for the other 5 possible starting weekdays for the month.

If you can state your answers to each of these scenarios, someone on here (perhaps me ) can probably come up with a formula solution.

 

[JPARKHURST]

Ack. I keep replying but site times me out before I am finished.

Very quickly, my months begin on first Sunday of the month. So if today is the 7th and is a Sunday, my month begins on the 7th.

I'm just really looking to distinguish the week of the month automatically for ease of use. It's not a huge deal, i just like to have things auto'd.

 

[StephenCrump]

Perhaps:

B2: =ROUNDUP((DAY(A2+1-WEEKDAY(A2)))/7,0)

Book1
	A	B
1	Date	WeekNo
2	Sat 28 Apr 2018	4
3	Sun 29 Apr 2018	5
4	Mon 30 Apr 2018	5
5	Tue 1 May 2018	5
6	Wed 2 May 2018	5
7	Thu 3 May 2018	5
8	Fri 4 May 2018	5
9	Sat 5 May 2018	5
10	Sun 6 May 2018	1
Sheet1

 

[JPARKHURST]

That looks like it just may work! thanks!