# Thread: Solved: UDF to emulate Weeknum()

1. ## Solved: UDF to emulate Weeknum()

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

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

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

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

5. Originally Posted by xld
=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

6. 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.

7. Originally Posted by RonMcK
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.

8. 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

9. 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.

10. Originally Posted by xld
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.

11. Originally Posted by RonMcK
Shazam,

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

Thanks,

Ron
Orlando, FL

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

12. Originally Posted by Shazam
Originally Posted by XLD
Originally Posted by RonMcK
Shazam,

How does an European calendar differ from a not European calendar?
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.

http://www.onlineconversion.com/day_week_number.htm
Apparently, XLD, a week is not a week in Europe or, rather, it is not necessarily a period of 7 contiguous days. The months and days are identical, as you noted, however the "European/ISO" calendar can divide a 'real' week (7 day span, Sun - Sat) into as many as 3 'weeks' when the week spans the year-end. Using Shazam's ul reference, I found that a week can be as short as 1 day. So, the European calendar can have more than one 53 week year in the four year cycle. Strange by true.

Ron
Orlando, FL

13. If you like to use the united states calendar week.

Then try.

=ROUNDDOWN((A2-DATE(YEAR(A2),1,1))/7,0)+1

Hope it helps!

14. Originally Posted by RonMcK
Apparently, XLD, a week is not a week in Europe or, rather, it is not necessarily a period of 7 contiguous days. The months and days are identical, as you noted, however the "European/ISO" calendar can divide a 'real' week (7 day span, Sun - Sat) into as many as 3 'weeks' when the week spans the year-end. Using Shazam's ul reference, I found that a week can be as short as 1 day. So, the European calendar can have more than one 53 week year in the four year cycle. Strange by true.

Ron
Orlando, FL
A week is a week wherever you are. What may be different is how you define where week 1 starts, whiuch is where the ISO week number definition is much tighter than MS's.

But BD wanted a WEEKNUM replacement, as found in Excel, using Monday as the start of the week. That is exactly what I gave him.

15. Originally Posted by xld
A week is a week wherever you are. What may be different is how you define where week 1 starts, whiuch is where the ISO week number definition is much tighter than MS's.

But BD wanted a WEEKNUM replacement, as found in Excel, using Monday as the start of the week. That is exactly what I gave him.
XLD,

My apologies, I meant to acknowledge that point several msgs back. I discovered how to enable Analysis Pack (for Excel and VBA) on Mac, so, I saw that your formula matched MS'.

In my last post, I only wanted to point out that ISO ends a year with a short week and begins the next year with another short week rather then have days outside a year included in the last/first week of either year.

Shazam,

Thanks, I'll try that formula when I get home.

Have a good evening/good night.

Ron
Orlando, FL

16. Well XLD's formula works perfectly for me - but thanks all for your input.