American Weeknum issue - Urgent!

gruntingmonkey

Active Member
Joined
Mar 6, 2008
Messages
413
Is there anyway of changing over to the European Week numbering system on Excel where Week 1 = w/c 4/1/10 and not w/c 28/12/9.

Ta
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you have a date in A2 this formula will give the "ISO week number" for that date

=INT((A2-WEEKDAY(A2,2)-DATE(YEAR(A2+4-WEEKDAY(A2,2)),1,4))/7)+2

With ISO week numbers week 1 always starts on the first Monday on or after 29th December, so in 2010 week 1 starts on 4th Jan.

With WEEKNUM(date,2) technically week 1 always starts on 1st Jan but then week 2 starts on the next Monday.....
 

gruntingmonkey

Active Member
Joined
Mar 6, 2008
Messages
413
Thats an issue - I have hundreds of files/cells that uses the week num function so dont want to change in each individual file. Is there anyway in overriding the Weeknum function?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
No you can't do it with WEEKNUM. You either have to use a formula like the one in my previous post or use VBA or download morefunc add-in and use the ISO.WEEKNUM function......
 

gruntingmonkey

Active Member
Joined
Mar 6, 2008
Messages
413
Sorry to bring this back to life - just a quickie.

The addin is great for single person use but I am having problems if there are multiple users using different PC's (not at the same time). The add in loads from my C Drive and when someone else loads it they have to delete out the filepath before their add in on their computer calculates it.

Is there anyway round this? Am currently having to Write Macro's for every file to manually populate the isoweeknum.

Ta
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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