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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

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
Gustav BrockCIOCommented:
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

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
JerryC4174Author Commented:
Thanks for the information this should do the trick!
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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