PDA

View Full Version : Solved: UDF to emulate Weeknum()

Sir Babydum GBE
10-01-2007, 02:42 AM
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
10-01-2007, 03:18 AM
=1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)

Shazam
10-01-2007, 06:02 AM
=1+INT(MOD(INT((A1-2)/7)+0.6,52+5/28))

Sir Babydum GBE
10-01-2007, 08:04 AM
Thanks both - that fixes my problem nicely (my formula problem, not my mental problem)

RonMcK
10-01-2007, 10:04 AM
=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

Bob Phillips
10-01-2007, 10:12 AM
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.

Shazam
10-01-2007, 10:18 AM
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
10-01-2007, 10:23 AM
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

Bob Phillips
10-01-2007, 10:24 AM
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
10-01-2007, 10:24 AM
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.

Shazam
10-01-2007, 10:32 AM
Shazam,

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

Thanks,

Ron
Orlando, FL

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

RonMcK
10-01-2007, 11:19 AM
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. Try testing this link below.

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

Shazam
10-01-2007, 11:26 AM
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!

Bob Phillips
10-01-2007, 01:13 PM
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.

RonMcK
10-01-2007, 01:43 PM
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

Sir Babydum GBE
10-01-2007, 02:27 PM
Well XLD's formula works perfectly for me - but thanks all for your input.