# I need a formula to calculate the week of the year using the IS0 8601 International Standard.

We recently started using the ISO 8601 International Standard calendar, but now, my formulas in ACCESS for calculating the week number do not work accurately.  I need a formula to calculate the week number using the ISO 8601 standard calendar.  I found this formula that works in EXCEL, but does not work in ACCESS:

=INT((NOW()-SUM(MOD(DATE(YEAR(NOW()-MOD(NOW()-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)
Microsoft Access

Last Comment
Arana (G.P.)
Arana (G.P.)

maybe you can adapt the one in here:
https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ
its ISO 8601 compliant
Arana (G.P.)

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
zepold

This is the criteria I use in my FISCAL_WEEK field of my query to pull the current week number, (this worked fine when we were going by fiscal week):

<Format(DatePart("ww",Date()),"00")

Basically, I need all the data up to, but not including, the current week.
zepold

Using the criteria, <Format(DatePart("ww",Date()),"00"), I still get data from this week.
zepold

I figured it out.  I had to define the first day of the week and the first week of the year:

<Format(DatePart("ww",Date(),2,2),"00")

zepold

Arana confirmed that I was using the correct criteria format, but after more investigating, I figured out that I needed to expand the Date function parameters.
Arana (G.P.)

oh sorry if datepart didnt work as expected, the other one sure works but its a little more elaborated (the one that uses floor() ),
I just read that the one with datepart might not give accurate results under certain conditions.

Since you are talking Fiscal Years then it means finances , so you  should be careful, read these
http://stackoverflow.com/questions/31560905/incorrect-iso8601-week-number-using-datepart
http://datapigtechnologies.com/blog/index.php/getting-week-number-in-access/
https://support.microsoft.com/en-us/kb/200299

you might encounter a bug, so the best option is to use 2 custom formulas and recreate the one used here:

So the best approach is to use the 2 formulas here: many date formulas

I put here only the needed ones for you to copy and paste in an access vba module
this one ACCURATELY gives result 53 for years with 53 weeks as opposed to the datepart function
that gives 52 as a result
msgbox(Format(DatePart("ww",CDate(31/12/2015),2,2),"00")) = 52         'this is wrong
(test it with MsgBox ISOWeekNum("31/12/2015")   = 53     'this is correct

``````Option Compare Database

Public Function YearStart(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function

Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))
End If

End Function
``````

this way you can even use it in a query
SELECT dDate, ISOWeekNum(dDate) FROM MyTable
zepold

I am not getting the results that I am looking for when I use the function above.  We have a calendar database that our finance group maintains and they have it populated up through the end of the year.  Using the ISO 8601 standard, they have calculated that 03/02/16 falls on Week 09.  I my query, I am looking for all data from Week 01 through the week prior to the current one.  When I run my query using either <Format(DatePart("ww",Date(),2,2),"00") or <IsoWeekNum(Date()), I get data that includes week 9.  Now, I am thinking that maybe the date function "Date()" is calculating this week as week 10.

Any way to compensate for that?
Arana (G.P.)

yes isoweeknum("03/02/16")
returns 9, which is correct

so if you just want up to number 8 just
isoweeknum("03/02/16")-1
or
isoweeknum(date()-7)
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY