Date formats in VB question

[KillGorack]

I have a week number (ISO), and a year. I need to get a date in mm/dd/yyyy on the Monday

for example a system we use provides this;
202308

And would love a bit of code to turn that into
02/20/2023

Not in a cell but with VB code

I'm wracking my brain...

 

[Eric W]

Try:

debug.Print Format(202308,"mm/dd/yyyy")

Edit: Oops, sorry, I thought that was a date serial. Let me take another look.

 

[JoeMo]

Maybe:

Sub GetDate()
Dim Inp, wkNum As Long, Yr As Long, Dte
Inp = InputBox("Enter Input in form yyyyWeekNumber")
Yr = Left(Inp, 4)
wkNum = Right(Inp, 2)
Dte = Evaluate("DATE(" & Yr & ", 1, -3 + 7 *" & wkNum & "- WEEKDAY(DATE(" & Yr & ", 1, 4), 2) + 1)")
MsgBox Format(Dte, "mm/dd/yyyy")
End Sub

 

[Eric W]

I had to do some digging into the ISO standard. I see Joe beat me with the code, but I get some different results with my formula. If Joe's doesn't work for you, try change the Evaluate line to:

Dte = Evaluate("DATE(" & Yr & ",1,1)+CHOOSE(WEEKDAY(DATE(" & Yr & ",1,1)),0,-1,-2,-3,-4,2,1)") + (wkNum - 1) * 7

This ensures that the date returned is always a Sunday.

 

[KillGorack]

I have a bunch of dates to check, once I check dates from lists with a few years worth of dates I'll come back.

Not the resolution i expected.. thought excel would have something canned.. but it looks promising..

 

[JoeMo]

My sub returns a Monday date per the OP - "I need to get a date in mm/dd/yyyy on the Monday"

 

[KillGorack]

Joe's code correlates perfect on the Monday of the week. It's perfect. I'll make a function for this one for sure I use it everywhere..

I appreciate you all!

 

[JEC]

This is all you need!

Based on the ISO-fact that every first thursday has to be in the new year

Function jec(year As Integer, week As Integer) As Long
jec = 7 * (week - 1) + DateSerial(year, 1, 4) - Weekday(DateSerial(year, 1, 4), 2) + 1
End Function

=jec(A1,A2)

If you want to know the date of an other day of the week: (change the day parameter):
This one below is also adapted to your situation.

Function jec(cell As Variant, day As Integer) As Long
jec = 7 * (Right(cell, 1) - 1) + DateSerial(Left(cell, 4), 1, 4) - Weekday(DateSerial(Left(cell, 4), 1, 4), 2) + day
End Function

=jec(A1,1)

 

[JoeMo]

Joe's code correlates perfect on the Monday of the week. It's perfect. I'll make a function for this one for sure I use it everywhere..

I appreciate you all!

Glad we could help.

 

[JEC]

One small change:

Function jec(cell As Variant, day As Integer) As Long
jec = 7 * (Right(cell, 2) - 1) + DateSerial(Left(cell, 4), 1, 4) - Weekday(DateSerial(Left(cell, 4), 1, 4), 2) + day
End Function

=jec(A1,1)