weeknum & VBA

**soakaos** · 01-05-2010, 10:30 AM

Hello hello,

Freshly registered on this forum, I'm here to find a little support with some of Excel functions I'm still not used too.. I'm very new in VBA so please, accept per advance my excuses if I'm not 100% clear with the terminology..

Well.. I got a file from my boss that is linked to an online table. This table contains customer contacts infos such as account number, activation date & time, etc, etc.. (1 contact = 1 line)

Now (for "quick filtering reasons" he said), my boss "needs" to have also in the sheet the week number for every contact we had..(as well as Month & Year)
and as the list grows up everyday with new entries, I thought I could use VBA..
so I wrote a little something that uses the date&time column to have the wanted infos added on every line :

Please Login or Register  to view this content.

It works fine with the Month & the year, both return the correct value in the right cells.

But I can't find a way to have it working with the weeknum one.. it returns me a 4 digit value...

For example, with "23.12.2009 09:07" (cell format = custom dd.mm.yyyy hh:mm), the weeknum returns me the value 5740 (when cell format is general, and "01.07.1905 00:00" when in date format) when it should returns me 52!

my only explanation is that it calculates the amount of weeks from 01.01.1900 to 23.12.2009 but yeah... it's not exactly what I need..

Does anyone know how I could solved this issue ? or how I could pass by ?

Thanks in advance for your answer,
I hope you'll understand my question, I'm not that used to write "technical" things in english..

merci merci,
steve

**JBeaucaire** · 01-05-2010, 10:50 AM

Hi Soakaos. Welcome. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used. (or just remove it...)

The WEEKNUM() formula needs one more parameter:

=WEEKNUM(G2,1)

**soakaos** · 01-05-2010, 11:48 AM

hi,

I've edited my post as recommended, thanks for the info and thanks for your quick answer.

Unfortunately, it doesn't work better with =WEEKNUM(G2,1) or =WEEKNUM(G2,2).. I'm still getting the same odd result.. but it looks like the correct result appears in the cell for less than a second and gets replaced by the 4 digit value.. there's no other code or formula in the sheet, so I really don't understand what goes wrong..

and if I write the formula myself directly on the worksheet, it works like it's supposed to..

but examples are better than words, so I've uploaded a light version of my file.. I hope some of you guys will take this as a challenge !!

Thanks in advance for your attention & answers,
steve

**JBeaucaire** · 01-05-2010, 01:28 PM

Your macro was written is such a way that it was adding formulas all over the place every time you made any sort of change anywhere.

Better, we'll just watch column F and enter the formulas in I, J, and K on the rows you just changed only. This should keep it fast and accurate, and the odd behavior goes away.

Please Login or Register  to view this content.

**soakaos** · 01-06-2010, 12:07 PM

hi,

thx again for your answer JBeaucaire. I've changed my code with yours and it works now effectively a lot better.

I also had an issue with the fact my company uses ISO week standards.. so I've searched and added this code into a module

Please Login or Register  to view this content.

and then changed "=weeknum(RC6)" into "=IsoWeekNumber(RC6)" and now it works perfectly.

Thanks again for your precious help,
and have a nice happy new year !!!!!