American Weeknum issue - Urgent!

[gruntingmonkey]

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

 

[barry houdini]

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]

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]

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]

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