![]() I'm almost certain this will break for some week at the end of some year-I just can't find that date yet. 4 years equals 1461 days and 209 weeks equals 1463 days. Here's what I mean-you've essentially carved 4 years (365 + 365 + 365 + 366) into 209 weeks (52 + 52 + 52 + 53) and the math doesn't work on a day level. Again, it works for all the examples I've plugged into it, but I'm having a hard time convincing myself your definition works for every date. It will return a value like this: '53-2016'. ![]() If you leave this argument empty, Sunday will be 1 and Monday will be 2 etc. Paste that into a module, then call it in a function like this: This argument tells the function how to number the days of the week. generated the following error & vbCrLf & vbCrLf & Error Number: & Err.Number. ' if WeekNum is 53 and not leap year, rolls over return value to next year MS Access VBA Determine the First Day of the Week. If (WeekNum = 53) And (YearNum Mod 4 > 0) Then TextBox2 WeekNum (.TextBox1) End With End Sub Function WeekNum (D As Date) As Integer WeekNum CInt (Format (D, 'ww', 2)) End Function Change the name of the textboxes and the sheet if needed. ' gets difference in weeks between DayOne and date passed (d) WeekNum = (DateDiff("ww", DayOne, d) + 1) ' gets date of sunday in first week of year If the calendar is Hijri, the returned integer represents the Hijri day of the week for the date argument. If Weekday(DayOne) > 1 Then ret = DateAdd("d", (Weekday(DayOne) - 1) * -1, DayOne) If the Calendar property setting is Gregorian, the returned integer represents the Gregorian day of the week for the date argument. ![]() ' returns Week and Year number of date (d): Day 1 of Week is first Sunday on or before January 1stĭim DayOne As Date ' holds first date of year (Sunday on or before 1/1)ĭim YearNum As Integer ' holds Year number of dateĭim WeekNum As Integer ' holds week number of date IntWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates. Except when it is a leap year where Week 53 exists. The last sunday of Dec is the beginning of Week 1 for the following year. I need the code to define that there are 52 weeks in a year. So I have gone through all their records (im the new guy) for a few years and deciphered this pattern: My work colleagues do not not what system is used to define the week numbers. hi all, Has anyone any idea how to get VBA to give the current week number I know i can use the analysis toolpak in. Im trying to write some VBA to convert a date into a week number.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |