ISO Week

[jcarroll01]

I would like to duplicate an excel formula that converts a date to ISO Week, but it is daunting to look at.

I would consider using a user created function for it, but have never had luck getting them to work.

Here is the excel formula:=INT(([DateField]-DATE(YEAR([DateField]-WEEKDAY([DateField]-1)+4),1,3)+WEEKDAY(DATE(YEAR([DateField]-WEEKDAY([DateField]-1)+4),1,3))+5)/7

Thanks

 

[Galaxiom]

DatePart function

http://office.microsoft.com/en-us/access-help/datepart-function-HA001228812.aspx

 

[HiTechCoach]

Here is a function for you to try:

Previous post here

 

[jcarroll01]

Galaxiom: That doesn't return the ISO week....but thanks

HTC: I read that post a couple of weeks ago and it requires 2 user defined functions AND I never got them both to work. More complicated than I was hoping for, but maybe I should revisit....

 

[namliam]

What if I may ask is an ISO Week? And I think most weeknumbering systems can be reflected using the (farly easy) format function.

 

[jcarroll01]

ISO 8601 has a standardization for dates / times that our company just recently settled on as well. As far as I can tell the rest of the computing world has not embraced it in any standard form and it takes a calculation, like the one above, to keep the week designations in check.

 

[namliam]

OK, accoording to wikipedia on ISO 8601, as I understand it:

TheDate = #2010-01-03#
?format(TheDate - weekday(TheDate, vbMonday) + 1 , "YYYY""W""WW",vbMonday, vbFirstFourDays) 
2009W53

Or the full set date:

TheDate = #2010-01-03#
?format(TheDate - weekday(TheDate, vbMonday) + 1 , "YYYY""W""WW",vbMonday, vbFirstFourDays) & format(weekday(thedate,vbMonday),"""-""0")
2009W53-7