Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

vba convert week number (and year) to date?

Tags:

excel

vba

formula

I have a week number in Cell C13 and a year in Cell C14.

I am using the below formula to convert this into the Thursday of that week number's date:

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

How could i do the same thing using VBA?

like image 945
user7415328 Avatar asked Feb 16 '17 10:02

user7415328


People also ask

How do I convert a number to a date in Excel VBA?

In VBA, there is a method through which we can convert a given string to a date. The method is known as the CDATE function in VBA. It is an inbuilt function in VBA, and the parts required for this function are first to convert the string to a number, then convert the given number to date.

How do I convert weeks to months in Excel?

Click the cell that you want to get month and type this formula =CHOOSE(MONTH(DATE(A2,1,B2*7-2)-WEEKDAY(DATE(B2,1,3))),"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") into it, then press Enter key to get the result, and then drag auto fill to ...

How does the Weeknum formula work in Excel?

The WEEKNUM Function[1] is an Excel DATE and TIME Function. It will return the week number of a specific date. The function will return an integer that represents a week number from 1 to 52 weeks of the year.


1 Answers

For a specific year, you can do it like this :

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

And to test it :

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

If others are looking into this and don't want a Thursday or don't work on 2017 :

  1. change the 5 to fit your need!

  2. Or use the below function GetDayFromWeekNumber

Code to test it :

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

And the generic function 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
like image 107
R3uK Avatar answered Oct 07 '22 16:10

R3uK