Avatar of zepold
zepoldFlag for United States of America

asked on 

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

Avatar of undefined
Last Comment
Arana (G.P.)
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Arana (G.P.)
Arana (G.P.)

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of zepold
zepold
Flag of United States of America image

ASKER

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.
Avatar of zepold
zepold
Flag of United States of America image

ASKER

Using the criteria, <Format(DatePart("ww",Date()),"00"), I still get data from this week.
Avatar of zepold
zepold
Flag of United States of America image

ASKER

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.
Avatar of zepold
zepold
Flag of United States of America image

ASKER

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.
Avatar of Arana (G.P.)
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

Open in new window


this way you can even use it in a query
 SELECT dDate, ISOWeekNum(dDate) FROM MyTable
Avatar of zepold
zepold
Flag of United States of America image

ASKER

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?
Avatar of Arana (G.P.)
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

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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo