Macro Help - WeekOfYear function

[Joe Blow]

Hello,

I am new to macros, so thought I might try it out. But I don't know
where to read about macros in VBA.

I am trying to substitute a formula I use very often with a VBA macro

here is the cell formula =ROUND((C13-DATE(YEAR(C13),1,1))/7,0)

Here is my macro attempt:

Function WeekOfYear(MyDate)
WeekOfYear = ROUND((MyDate-DATE(YEAR(MyDate),1,1))/7,0)
End Function

I used a cell reference of WeekOfYear(C13), but no luck.

If someone could point me in the right direction, I would greatly
appreciate it.

Joe

 

[Chip Pearson]

Joe,

It depends on what is considered the first week of the year. See
www.cpearson.com/excel/weeknum.htm for details and examples.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[LanceB]

VBA and Excel have some differences in funtions --- see vba help for DATE --
returns the system date etc


Function WeekOfYear(MyDate)
WeekOfYear = Round((MyDate - DateSerial(Year(MyDate), 1, 1)) / 7, 0)
End Function

Lance

 

[Joe Blow]

Joe,

It depends on what is considered the first week of the year. See
www.cpearson.com/excel/weeknum.htm for details and examples.

Thanks Chip, very helpful. I enabled ATP to use weeknum.

Joe.

 

[Arvi Laanemets]

P.e. here is one I have used (the week starts with Monday, the 1st week of
year is one with 1st Thursday of year in it (date is in A1, probably you
have replace semicolons in formula with commas):
=IF(INT((G2 - DATE(YEAR(G2);1;1)
+1+7-WEEKDAY(G2;2)+4)/7)=0;INT(((DATE(YEAR(G2)-1;12;31) -
DATE(YEAR(G2)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(G2)-1;12;31);2)+4)/7);IF(AND(INT
((G2 - DATE(YEAR(G2);1;1)
+1+7-WEEKDAY(G2;2)+4)/7)=53;WEEKDAY(DATE(YEAR(G2);12;31);2)<4);1;INT((G2 -
DATE(YEAR(G2);1;1) +1+7-WEEKDAY(G2;2)+4)/7)))

I had a besser (shorter) version somewhere also, but couldn't find it at
moment.

There is a WEEKNUM function in Excel too (with Analysis Toolpack activated),
but it counts weeks only in a single year, i.e. the year can end p.e. with
1-day (53th) week, and then next year starts with 6-day (1st) week (or
contrary). It's nonsens - a week is always 7 days long!


Arvi Laanemets

 

[Joe Blow]

VBA and Excel have some differences in funtions --- see vba help for DATE --
returns the system date etc


Function WeekOfYear(MyDate)
WeekOfYear = Round((MyDate - DateSerial(Year(MyDate), 1, 1)) / 7, 0)
End Function

Lance

Thanks Lance. I'll check out the vba help.

Joe