Absolute Week in a Month

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
261
Platform
  1. Windows
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
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 19, 2014
Messages
261
Platform
  1. Windows
@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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top