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

xld
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

xld
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

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

xld
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

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!

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

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.