Convert formula to Function

Hi,

How to make the following as a Macro Function so I can call it with WeekNo(A1) where A1 would contain the Date...

=IFERROR(INT((DATEDIF((DATE(YEAR(A1),4,6)-WEEKDAY(DATE(YEAR(A1),3,31))-7), A1, "d") / 7))+1,INT((DATEDIF((DATE(YEAR(A1)-1,4,6)-WEEKDAY(DATE(YEAR(A1)-1,3,31))-7), A1, "d") / 7))+1)

Thanks
MirageSFAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Bill PrewCommented:
Care to share what you think that formula does / what you want the function to do?  Can you share the link to where you got that formula.


»bp
0
 
MirageSFAuthor Commented:
Hi, it basically calculates number of weeks since last Monday in March of each year, between 1 and 52 or 1 and 53 in some years.
0
 
MirageSFAuthor Commented:
Infact, I just rewrote the formula and can now achieve the results using...

=1+INT((J2-DATE(YEAR(J2)-(J2<DATE(YEAR(J2), 3,26)),3,26))/7)

But struggling to make it a function!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
How does that always find the last Monday of March, for any given year?


»bp
0
 
MirageSFAuthor Commented:
The date in J2 past to it will always be a Monday, the calculation will either use the last Monday as week 1 or first Monday of April as week one, depending on whether its a 52 or 53 week year.
0
 
Bill PrewCommented:
Try this:

Function ElapsWeeks(d As Date) As Integer
    If d < DateSerial(Year(d), 3, 26) Then y = 1 Else y = 0
    If IsDate(d) Then ElapsWeeks = 1 + Int((d - DateSerial(Year(d) - y, 3, 26)) / 7)
End Function

Open in new window


Updated: Changed remaining reference to datIn to d

»bp
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
MirageSFAuthor Commented:
Thanks mate, had to change datIN to make it work but now great :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.