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)
zepoldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
maybe you can adapt the one in here:
https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ
its ISO 8601 compliant
aranaCommented:
or you can just use DatePart(“ww”,Date())  in access (not sure if its compliant)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zepoldAuthor Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

zepoldAuthor Commented:
Using the criteria, <Format(DatePart("ww",Date()),"00"), I still get data from this week.
zepoldAuthor Commented:
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")

Thank you for your help.
zepoldAuthor Commented:
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.
aranaCommented:
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

Open in new window


this way you can even use it in a query
 SELECT dDate, ISOWeekNum(dDate) FROM MyTable
zepoldAuthor Commented:
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?
aranaCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.