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

zepold
zepold used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
maybe you can adapt the one in here:
https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ
its ISO 8601 compliant
or you can just use DatePart(“ww”,Date())  in access (not sure if its compliant)

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

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.

Author

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.
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

Author

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?
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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial