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

=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

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

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.

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

Try testing this link below.

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!

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.

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.