All geek questions in one place

Vba convert weekly number (and year) to date?

I have the number of the week in cell C13 and the year in cell C14.

I use the formula below to convert it on Thursday of this week :

=DATE(C14,1,-6)-WEEKDAY(DATE(C14,1,3))+C13*7

How can I do the same with VBA?

+5
source share
3 answers

For a specific year, you can do this as follows:

 DateAdd("ww", WeekNumber - 1, DateSerial(2017, 1, 5))

And check this out:

 Debug.Print Format(DateAdd("ww", WeekNumber - 1, DateSerial(YearNumber, 1, 5)), "ddd d MMM yy")

If others look at it and do not want Thursday or do not work in 2017 :

  • change 5 to suit your needs.

  • Or use the following function GetDayFromWeekNumber

Verification Code:

 Sub test() Debug.Print Format(GetDayFromWeekNumber(2017, 1, 4), "ddd d MMM yyyy") End Sub

And the general function of GetDayFromWeekNumber :

 Public Function GetDayFromWeekNumber(InYear As Integer, _ WeekNumber As Integer, _ Optional DayInWeek1Monday7Sunday As Integer = 1) As Date Dim i As Integer: i = 1 If DayInWeek1Monday7Sunday < 1 Or DayInWeek1Monday7Sunday > 7 Then MsgBox "Please input between 1 and 7 for the argument :" & vbCrLf & _ "DayInWeek1Monday7Sunday!", vbOKOnly + vbCritical 'Function will return 30/12/1899 if you don't use a good DayInWeek1Monday7Sunday Exit Function Else End If Do While Weekday(DateSerial(InYear, 1, i), vbMonday) <> DayInWeek1Monday7Sunday i = i + 1 Loop GetDayFromWeekNumber = DateAdd("ww", WeekNumber - 1, DateSerial(InYear, 1, i)) End Function
+3
source

This works fine, given that the first day of the week is Sunday:

 Function fnDateFromWeek(ByVal iYear As Integer, ByVal iWeek As Integer, ByVal iWeekDday As Integer) fnDateFromWeek = DateSerial(iYear, 1, ((iWeek - 1) * 7) + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1) End Function

As the third parameter, you must pass that day. Thursday - Day No. 5. Credits to these guys: http://www.dreamincode.net/forums/topic/111464-calculate-date-from-year-weeknr-and-daynumber/

+1
source

As an additional option for those who use the ISO weekly number system, where week 1 is the first week of the year containing four days (or a week that includes the first Thursday of the calendar year and the first day of the week is Sunday.

  • DOW is an optional argument representing the day of the week. It will be in effect until Thursday, and since we use the ISO indexing system, it should always fall under the current year.
 Option Explicit Function WNtoDate(WN As Long, YR As Long, Optional DOW As Long = 5) As Date 'DOW: 1=SUN, 2=MON, etc Dim DY1 As Date Dim Wk1DT1 As Date Dim I As Long DY1 = DateSerial(YR, 1, 1) 'Use ISO weeknumber system I = DatePart("ww", DY1, vbSunday, vbFirstFourDays) 'Sunday of Week 1 Wk1DT1 = DateAdd("d", -Weekday(DY1), DY1 + 1 + IIf(I > 1, 7, 0)) WNtoDate = DateAdd("ww", WN - 1, Wk1DT1) + DOW - 1 End Function
+1
source

Source: https://habr.com/ru/post/1264296/

More articles:


All Articles