CONVERT WORK WEEK TO DATE

IN ACCESS I WANT TO BE ABLE TO CONVERT A YEAR AND WORK WEEK NUMBER TO A DATE.
JerryC4174Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
For the ISO standard weeknumber you can use this function to obtain the date of a specific weekday of any year-week:

Public Function ISO_DateOfWeek( _
  ByVal intYear As Integer, _
  ByVal bytWeek As Byte, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

  ' The fourth of January is always included in
  ' the first week of year intYear.
  Const cbytDayOfFirstWeek  As Byte = 4
  ' Number of days in a week.
  Const cbytDaysOfWeek      As Byte = 7
  ' Month of January.
  Const cbytJanuary         As Byte = 1
  
  Dim datDateOfFirstWeek    As Date
  Dim intISOWeekday         As Integer
  Dim intWeekdayOffset      As Integer
  
  ' No specific error handling.
  On Error Resume Next
    
  If intYear > 0 Then
    ' Date of fourth of January in year intYear.
    datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
    
    ' Weekday of requested weekday.
    intISOWeekday = Weekday(bytWeekday, vbMonday)
    ' Calculate offset from requested weekday in first week of year intYear.
    intWeekdayOffset = intISOWeekday - Weekday(datDateOfFirstWeek, vbMonday)
    
    ' Date of requested weekday in first week of year intYear.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
    ' Date of requested weekday in requested week of year intYear.
    datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
  End If
  
  ISO_DateOfWeek = datDateOfFirstWeek
  
End Function

Open in new window

0
 
PatHartmanCommented:
See if this gets what you want:

print dateadd("d", (Format(#3/23/18#, "ww") -1) * 7, cdate("01/01/" & Year(#3/23/18#)))

Substitute your variables for the hard coded date in both places.
0
 
JerryC4174Author Commented:
Thanks for the information this should do the trick!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PatHartmanCommented:
Nice function Gus.  I hope you don't mind that I added it to my useful dates functions database.  I attributed it to you.  Is that correct?
UsefulDateFunctions180327.zip
0
 
JerryC4174Author Commented:
Tried it out today and it worked perfectly.  Thanks again.
0
 
Gustav BrockCIOCommented:
You are both welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.