I was travelling last days, so I could not participate on this interesting
thread resulting from my question... just 29 messages in four days. I think
you have show all subtle points about WEEK() function and ISO sorting across
I was wrong on WEEK() result for current date, as it shows the right week
number if formatting Monday as first day, but only if you do not use it on
end/beggining year weeks.
To me, there are two things to note: first, we may need a function grouping
data on weeks across different years, and second, we may need a function
that respects ISO week formatting, and so week 1999-0 should be week 1998-53
(it could be 52 on other years) on this new function (I support ISOWEEK()
name, as Peter has suggested ...)
Sometime ago I received an Excel 97 VBA function that allows to choose the
result being formatted as "53" or "199853", using a control:
ISOWEEK(date) = 53
ISOWEEK (date,0) = 53
ISOWEEK (date,1) = 199853
being eg. date="1998-12-28" to "1999-1-3".
I can e-mail it to you, should anyone be interested on it (yes, is a
worksheet with macros, but no viruses on it).
Thank you very much for your support,
Yes, the right way to fix this is to introduce a new function.
(The problem is that WEEK() in ODBC is defined to only return the week
number, so we can't fix the WEEK() function)
I shall put this on your TODO.