Absolute Week in a Month

[Juggler_IN]

I have figured out how to code the Absolute Week in a Year for a given date. How can this be modified to Absolute Week in a Month for a given date? For example, 1st September is Wk 35 and 2nd till 8th September is Wk 36 (absolute weeks with Wk 1 starting on a Wednesday = 1/1/2020).

An Absolute Week counts weeks starting from January 1st, no matter what day of the week it is. In other words, January 1st through 7th is always week one. If January 1st is a Tuesday, than each week of the year will be Tuesday through Monday and tallied accordingly.

Public Function AbsWk(x As Date) As Integer
    AbsWk = VBA.Int(((x - VBA.DateSerial(VBA.Year(x), 1&, 0&)) + 6&) / 7&)
End Function

 

[Joe4]

Wouldn't this logic work?
- Add 6 to the day of the month, divide by 7, and keep the integer portion.

For example, for the first day of the month, it would look like:
Day 1: (1 + 6)/7 = 1
Day 23: (23 + 6)/7 = 4.12857 = 4 (when dropping the decimal)

That function would look something like:

Public Function AbsWkMn(x As Date) As Integer
    AbsWkMn = Int((Day(x) + 6) / 7)
End Function

 

[Juggler_IN]

@Joe4 ; Actually does not work.

If i plot all the dates for 2020 starting 1/1/2020, which is a Friday, then from 1st to 7th Jan is tallied from Friday to Thursday.

And, 1st September is on a Thursday (last way of Friday to Thursday week, where Fri = 26th Aug, ..., Wed = 31st Aug, and Thu = 1st Sep.

So, 1st Sep is in Week 1 of month September. And, thus 2nd till 8th is Week 2 of month September.

The UDF shared gives 1st to 7th Sep as Week 1 of September.

 

[Joe4]

Sorry, I did not realize that you wanted this function dependent upon Absolute Week of the Year.
I thought you just wanted an Absolute Week of the Month, that worked similarly to Absolute Week of the Year, but was not dependent upon it.
So each month worked independently of each other (much like each year would).

What you want is much more complex. Not sure the best way to do that.