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

Microsoft ExcelMicrosoft Office

Last Comment

MirageSF

8/22/2022 - Mon

Bill Prew

10/29/2017

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

MirageSF

10/29/2017

ASKER

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.

MirageSF

10/29/2017

ASKER

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!

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Bill Prew

10/29/2017

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

»bp

MirageSF

10/29/2017

ASKER

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.

ASKER CERTIFIED SOLUTION

Bill Prew

10/29/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

MirageSF