So for 03-Mar-13 instead of taking 3 and dividing it by 7, I'm taking 8 and dividing it by 7. To figure out the offset, I have to get the weekday for the first day of the month. Now, you can get the results supplied by the previous answers here by using my method, too, if you just offset the day. The reason for Banker's rounding is to eliminate the upward bias that accumulates if you try to round a whole bunch of numbers. For instance, if the windshield wipers are on in mine and I shift into reverse then the rear wipers go on, which at first seems like an electrical glitch, rather than a feature. 5 part either up or down, reminds me of how cars today try to outsmart us. This type of rounding, which can round the. I don't think that using Banker's rounding was a wise engineering decision, but it's what they decided to use. If you do want to round in VBA, then take care that you're not trying to round up an integer, which is already rounded (and maybe not in the way that you wanted).Īlso note that, probably to try to confuse us, VBA's Round function uses Banker's rounding ( round-half-even), whereas Excel's rounding does not - and nor does CInt, which also works differently from Int (and Fix), which truncate the fractional part. But I think that the method above is fairly to-the-point. There is also a C++ discussion of rounding up only that is not hard to follow. Stack Overflow provides a discussion of some user-contributed rounding functions in VBA, but the answers are inconsistent. The rest of this here is just additional discussion about the algorithms, rounding, and optimization that you can read if you're interested, but the main answer is above. Which could well be faster in some cases than the VBA version. If InputDbl = Round(InputDbl, Digits) Then RoundUpVBA = InputDbl Else RoundUpVBA = Round(InputDbl - 0.5 / (10 ^ Digits), Digits)Īlso, here is the Excel formula for user3496574's answer, which calculates the week number the other way: =WEEKNUM(MyDate) - WEEKNUM(EOMONTH(MyDate,-1)+1)+1 If InputDbl = Round(InputDbl, Digits) Then RoundUpVBA = InputDbl Else RoundUpVBA = Round(InputDbl + 0.5 / (10 ^ Digits), Digits) WeekOfMonth = RoundUpVBA(Day(TestDate) / 7,0)įunction RoundUpVBA(InputDbl As Double, Digits As Integer) As Double To be as complete as possible, here is a solution that is slightly longer but doesn't access an Excel function, and since it uses fairly simple logic it might be more efficient if more data is involved: Function WeekOfMonth(TestDate As Date) As Integer VBA doesn't have a round up function built-in, but obviously this would work: Function WeekOfMonth(TestDate As Date) As Integer So in Excel it is something like: =ROUNDUP(DAY(MyDate)/7,0) But technically if you want to count weeks as blocks of 7 days that begin on the 1st of the month - which is what I wanted to do - then you need to get the day of the month, divide by 7, and then round up. I don't know that either way is right or wrong. It depends on how you want to count the weeks.
#VBA WEEK NUMBER FULL#
So the results can go up to 6, whereas if you are counting by the full week, you can go up only to 5 (and February obviously has only 4 full weeks). To put it another way, the methods given by guitarthrower, rvalerio, and user3496574 are equivalent to counting the rows on a monthly calendar, as opposed to counting the full weeks starting at day one.
There are a few clever answers here, however the question did say, "For instance 03-Mar-13 would give week 1 of march." The answers provided here will return week 2 for that date because March 1st is day 6 of a week, which makes March 3rd day 1 again, and thus part of week 2. To DateSerial(Year(selDate), Month(selDate) + 1, 1) To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 27)Ĭase DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 28) _ To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 20)Ĭase DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 21) _ To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 13)Ĭase DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 14) _ To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 6)Ĭase DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 7) _ To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 - 1)Ĭase DateSerial(Year(selDate), Month(selDate), StartOfWeek2) _ = Function WeekOfMonth(selDate As Date)ĭayOfFirst = Weekday(DateSerial(Year(selDate), Month(selDate), 1), vbSunday)Ĭase DateSerial(Year(selDate), Month(selDate), 1) _
#VBA WEEK NUMBER CODE#
This is a UDF and can be used on a spreadsheet or in code.This isn't the most elegant code, but it worked for me.
0 kommentar(er)
