Solved: UDF to emulate Weeknum()

[Sir Babydum GBE]

Hi

Does anyone know of such a UDF (or formula)? The analysis toolpack is not available on all PC's here, and they're locked down so that I can't install it.

I'm after a UDF/Formula that will look at a date and return the week number (Mondays being the beginning of the new week)

Thanks

BD

[Bob Phillips]

=1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)

[Shazam]

=1+INT(MOD(INT((A1-2)/7)+0.6,52+5/28))

[RonMcK]

=1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)

XLD,

Since your private msg box is full, I'm uploading here. I believe your algorithm isn't quite right, yet. First year (2007) begins w/ week 1 and ends with week 53 (a 2 day week?), year 2 begins with week 2 and ends with week 53 as do years 3 and 4.


Ron
Orlando, FL

[Shazam]

XLD,

Since your private msg box is full, I'm uploading here. I believe your algorithm isn't quite right, yet. First year (2007) begins w/ week 1 and ends with week 53 (a 2 day week?), year 2 begins with week 2 and ends with week 53 as do years 3 and 4.
Ron
Orlando, FL

Sir Babydum GBE is runing off the european calendar not the united states calendar.

[RonMcK]

XLD,

I'll have to test my worksheet when I get home; I'm on a Mac (2004), here at work, and it doesn't have Analyisis Pack loaded (if that's even available for Excel Mac).

Shazam,

How does an European calendar differ from a not European calendar?

Thanks,

Ron
Orlando, FL

[Shazam]

Shazam,

How does an European calendar differ from a not European calendar?

Thanks,

Ron
Orlando, FL

Try testing this link below.

http://www.onlineconversion.com/day_week_number.htm

[Sir Babydum GBE]

Thanks both - that fixes my problem nicely (my formula problem, not my mental problem)

[Bob Phillips]

Well as I see it, mine gives exactly the same answer as MS' WEEKNUM, which is what was asked for.

I think you are missing the fact the BD said the week starts on Monday.

[Bob Phillips]

AFAIAA, there is no difference in a European and a US calendar. Each month has the same nuumber of days, the day/dates are the same, et al.

[Bob Phillips]

AFAIAA, there is no difference in a European and a US calendar. Each month has the same nuumber of days, the day/dates are the same, et al.