DutchKevin
Board Regular
- Joined
- Apr 13, 2011
- Messages
- 133
- Office Version
- 365
- Platform
- Windows
Hello All,
it's been a while since my last post. that would mean everything is working as it should be![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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
First I tried to connect a year to this date, (with the date in 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
But still same result.
Do any you have a hint to a structured sollution? a UDF is also welcome
Thanks already for looking
Kevin
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
Code:
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)
Code:
=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
Code:
=1*(YEAR(EOMONTH(E2;0)-15)&IF(LEN(WeekNumberISO(E2))=1;0;"")&WeekNumberISO(E2))
Do any you have a hint to a structured sollution? a UDF is also welcome
Thanks already for looking
Kevin