date to yyyyww format

[DutchKevin]

Hello All,
it's been a while since my last post. that would mean everything is working as it should be

But now i face a challenge again.
I need to convert a standard date to the (european) yyyyww format.

I already have an ISOweek UDF

Public Function WeekNumberISO(InDate As Date) As Long
    Dim D As Date
    D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3)
    WeekNumberISO = Int((InDate - D + Weekday(D) + 5) / 7)
End Function

First I tried to connect a year to this date, (with the date in E2)

=1*(YEAR(E2))&IF(LEN(WeekNumberISO(E2))=1;0;"")&WeekNumberISO(E2)

But when i checked for 31-12-2012 it returned 201201
But I would need it to return 201301. It's the last day of 2012, but according ISO it is in week 1 on 2013.

Then i thought to avoid the end of month issue with this

=1*(YEAR(EOMONTH(E2;0)-15)&IF(LEN(WeekNumberISO(E2))=1;0;"")&WeekNumberISO(E2))

But still same result.

Do any you have a hint to a structured sollution? a UDF is also welcome
Thanks already for looking
Kevin

 

[barry houdini]

Hello Kevin,

Try this to get correct ISO year and week

=YEAR(E2-WEEKDAY(E2;3)+3)&TEXT(weeknumberiso(E2);"00")

 

[DutchKevin]

Hi Barry,
This works perfectly. Very smart thinking.
You also provided me with a good sollution for the 1 digit weeknumbers.
I'll store it in my very usefull tricks folder as well

Thanks
Kevn

 

[DutchKevin]

Hello,
Can you also suggest a translation of this to a UDF? So i can use it in VBA?

Thanks Kevin

 

[DutchKevin]

Hello,
In a formula this works perfectly.
Can anyone suggest a translation of this to a UDF? So i can use it in VBA?

Thanks Kevin