On Wed, 4 Feb 2009 02:05:03 -0800, Kriss
wrote:

Hi

I have an similar problem.
However, I also want to display the year, including the week number.

My problem is;
31.12.09 = week 53 year 2009
01.01.10 = week 53 year 2010

But I want the entire week from 28.12.09 - 03.01.10 to be displayed as week
53-2009.

I hope anyone can help me on this matter.

If I understand you correctly, it seems you want to calculate the weeknumber
according to the ISO standard, which has week 1 starting on the Monday that
includes the first Thursday of the year.

The Excel WEEKNUM function does not make that calculation.

You can do it with a VBA UDF. To enter this, <alt-F11 opens the VBEditor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens. To use
this, enter a formula of the type =ISOWeeknum(cell_ref) into some cell, where
cell_ref contains a date.

Since you don't write how you want to display the year in this message, I can't
help you with that. But consider how you want to display the year for 01.01.10

===========================================
Function ISOWeeknum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
ISOWeeknum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
==========================================
--ron