Solved: UDF to emulate Weeknum()

Printable View

10-01-2007, 02:42 AM
Sir Babydum GBE

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

=1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)
10-01-2007, 06:02 AM
Shazam

=1+INT(MOD(INT((A1-2)/7)+0.6,52+5/28))
10-01-2007, 08:04 AM
Sir Babydum GBE

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

Quote:

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

Quote:

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

Quote:

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.
10-01-2007, 10:32 AM
Shazam

Quote:

Originally Posted by RonMcK

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
10-01-2007, 11:19 AM
RonMcK

Quote:

Originally Posted by Shazam

Quote:

Originally Posted by XLD

Quote:

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.

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

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

Quote:

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

Quote:

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
10-01-2007, 02:27 PM
Sir Babydum GBE

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

My head hurts after reading all that.

P.S. On planet BeeDee there are 8 days a week, 1 is a working day, and seven are rest days. Oddly, the planet's rotation around its sun is such that the rest days are 30 hours long, and the work days are 30 minutes long.

If I ever have to do a preadsheet there - I'll ask.