VBA Convert date to week number
In VBA I want to convert a date as 03/11/2017(DD/MM/YYYY) into the week number for that date.
Until now I have the following code:
'geting the date out of the string HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10) 'switch "." to "/" HeadlineTemp = Replace(HeadlineTemp, ".", "/") 'convert to a date FristVRFirstKW = CDate(HeadlineTemp)
Now, I need a function to convert that date into the week number of the year. First week day is Monday.
To make the Week Number with Monday as a first day, use the following:
Finding the week number using VBA in Microsoft Excel, Excel has in built function “WEEKNUM” to find the week number. Date parameter specifies the date for which one wants to find the week number and FirstDayOfWeek specifies the day which needs to be considered as the first day of the week. Convert date to week number with formulas. On the other hand, you can also apply the WEEKNUM function to convert a date to corresponding week number. 1. Select a blank cell you will return the week number, enter this formula: =WEEKNUM(B1,1), and press the Enter key. See screenshot:
Using VBA, to convert a date into an isoWeeknumber, you merely need the
DatePart function (where DT is the date of interest):
isoWeekNumber = DatePart("ww", DT, vbMonday, vbFirstFourDays)
If you want to use other definitions than that specified in ISO 8601, investigate some of the other options for
As pointed out by @Mike85, there is a bug in
DatePart (and also in the
Format) function wherein Monday may be erroneously given a weeknumber of 53 when it should be 1.
There are a variety of workarounds.
In Excel 2013+ (Excel for Mac 2011+) you can use for the ISO Weeknumber:
isoWeekNumber = WorksheetFunction.isoWeekNum(dt)
For earlier versions, you can test the Monday and adjust it if necessary, or you can write a separate routine.
WorksheetFunction.WeekNum method (Excel), The WeekNum function considers the week containing January 1 to be Visual Basic for Applications (VBA) calculates serial dates differently� Hi all, I need the VBA code for the following! I need to convert a DATE entered in lets say TextBox 1 and have the Week Number for that particular Date in TextBox 1 displayed in TextBox 2 Can anyone help me with this as it is rattling my brain! Thanks in advance guys!
Be carefull when it comes to week numbers as there are different definitions around. The Excel definition differs from the ISO definition. To get the ISO weeknumber use (copied From http://www.rondebruin.nl/win/s8/win001.htm)
Public Function IsoWeekNumber(d As Date) As Integer Dim d2 As Long d2 = DateSerial(Year(d - Weekday(d - 1) + 4), 1, 3) IsoWeekNumber = Int((d - d2 + Weekday(d2) + 5) / 7) End Function
VBA for smarties ISO weeknumber, VBA comprises 2 functions that deliver the weeknumber of a date: DatePart and Format. DatePart results in a number, Format retuns a string. Here we discuss the � This is rather an addition to this closed topic (Convert date to week number), than a real question. But it might help some people. But it might help some people. The idea is I want to create a variable that simply is the week of the year, so an integer between 1 and 53.
WeekdayName( number, [abbreviate], [firstdayofweek] )
WeekdayName(2, TRUE, vbMonday)
Date to week number using VBA, Hi all, I need the VBA code for the following! I need to convert a DATE entered in lets say TextBox 1 and have the Week Number for that� This is because Excel stores date as serial numbers, so the value 43586 is equal to the date “01-05-2019” and if you increase the number by 1 i.e. 43587 the date will be “02-05-2019”. Code: Sub Date_Format_Example3() Dim MyVal As Variant MyVal = 43586 MsgBox Format(MyVal, "DD-MM-YYY") End Sub
So, this is my final and working perfectly version
Public Function IsoWeekNumber(d As Date) As String Dim kwtemp As String kwtemp = DatePart("ww", d, vbMonday, vbFirstFourDays) If Len(kwtemp) = 1 Then kwtemp = "0" & kwtemp IsoWeekNumber = kwtemp End Function If Application.International(xlMDY) = True Then HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10) HeadlineTemp = Replace(HeadlineTemp, ".", "/") HeadlineTemp = Mid(HeadlineTemp, 4, 3) & Left(HeadlineTemp, 2) & Right(HeadlineTemp, 5) VRFirstKW = CDate(HeadlineTemp) HeadlineTempEndKW = Mid(VRHeadline, InStr(VRHeadline, "]") - 10, 10) HeadlineTempEndKW = Replace(HeadlineTempEndKW, ".", "/") HeadlineTempEndKW = Mid(HeadlineTempEndKW, 4, 3) & Left(HeadlineTempEndKW, 2) & Right(HeadlineTempEndKW, 5) VREndKW = CDate(HeadlineTempEndKW) VRKW = "KW" & IsoWeekNumber(VRFirstKW) & "-" & IsoWeekNumber(VREndKW) & "/" & Year(VREndKW) Else 'don't switch position of the month with days HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10) HeadlineTemp = Replace(HeadlineTemp, ".", "/") VRFirstKW = CDate(HeadlineTemp) HeadlineTempEndKW = Mid(VRHeadline, InStr(VRHeadline, "]") - 10, 10) HeadlineTempEndKW = Replace(HeadlineTempEndKW, ".", "/") VREndKW = CDate(HeadlineTempEndKW) VRKW = "KW" & IsoWeekNumber(VRFirstKW) & "-" & IsoWeekNumber(VREndKW) & "/" & Year(VREndKW)
VBA To return week number? - Excel General, =TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date whose week number is to be returned. The VBA code for this function is: The formula to return the Start date of the week is as follows: =DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7. Where A2 is the year and B2 is the week number. Please note that the formula returns the date as a serial number, and to have it displayed as a date, you need to format the cell accordingly.
Week Numbers In Excel, This VBA function can be used for converting date into week number of the month . Discussion: Use this function in those cases where weekly reporting is� With the date January 5, 2016 in B5, WEEKNUM returns 2 as the the week number. Note: The date must be in a form that Excel recognizes as a valid date. How this formula works: The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number.
Excel WEEKNUM function – convert week number to date and vice , This Excel tutorial explains how to use the Excel WEEKNUM function with syntax and examples. The Microsoft Excel WEEKNUM function returns the hours (a� VBA CDate()function is one of the most commonly used date functions to convert a date stored in a “String” variable into a date data type. The example given below will help make the concept clear. Open your MS Excel and enter the date in cell A1 in the following format: March 24, 2014
Excel - Week Number Of The Month, The WeekDay function takes Date as a parameter and returns a number between 1 and 7, that is the week day of the date provided. Format: WeekDay (strDate [Firstdayofweek]) Arguments: strDate. Mandatory; Type: Date; Date, whose Week Date need to be calculated. Firstdayofweek. Optional; Type: Numeric; Specifies the first day of the week
- Excel has a built-in formula for this (
- @ImmaginaryHuman Yes, I know that in Excel is that built in formula, but apparently, in VBA is not... so I need to create one.
- Try this:
MsgBox "We are currently in week " & Application.WorksheetFunction.WeekNum(DateSerial(2017, 10, 27))
- Thanks Vityata, and also thanks for helping me with editing.
- Thanks for the hint. Definetly I will try to do this.
- This function have bugs for leap year. Need to get around. See my answer.
- @mike85 Thanks for pointing that out. I was not aware of that bug in
2017when I wrote this answer.
- Thanks @FunThomas, this help.
- Nice information but doesn't actually address the question which asks for a week number from a date (and is actually answered by Ralph's comment)