# Absolute Week in a Month

#### Juggler_IN

##### Active Member
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.

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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### 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:
VBA Code:
``````Public Function AbsWkMn(x As Date) As Integer
AbsWkMn = Int((Day(x) + 6) / 7)
End Function``````

#### Juggler_IN

##### Active Member
@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.

Replies
4
Views
123
Replies
2
Views
140
Replies
4
Views
146
Replies
7
Views
762
Replies
1
Views
112

1,127,524
Messages
5,625,309
Members
416,092
Latest member
dodovisk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back