This VBA function returns the number of weeks that one date is away from another, in whole weeks. For example, any date in the third week from the starting date will return a value of 3.
After the difference between the two dates is divided by 7 to get weeks, the VBA function Int() is used to round the number of weeks to just the integer. Then 1 is added because the starting date defines week 1.
You could also do the calculation using the Mod() function, which could be used to calculate the remainder that needs to be subtracted so that just an integer remains:
intAge = ((CollectionDate - StartDate) / 7) - _
(((CollectionDate - StartDate) Mod 7) / 7) + 1
Public Function GetAgeInWeeks(StartDate As Date, CollectionDate As Date) As Integer
' This function returns the age in weeks of the CollectionDate, starting
' with StartDate which defines week 1. For example, any CollectionDate
' in the third week from StartDate will return a value of 3. If CollectionDate
' is less than StartDate, then a 0 is returned.
' GetAgeInWeeks() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact firstname.lastname@example.org.
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_GetAgeInWeeks
Dim intAge As Integer
Dim strPrompt As String
' If CollectionDate is less than StartDate, then return a 0 and exit.
If CollectionDate < StartDate Then
GetAgeInWeeks = 0
' If the StartDate equals the CollectionDate, then use 1 because
' StartDate defines the first day of week 1.
If StartDate = CollectionDate Then
intAge = 1
' Get the difference between the CollectionDate and the StartDate
' and divide by 7 to get weeks. Get the integer value and add 1.
intAge = Int((CollectionDate - StartDate) / 7) + 1
' Return the age in weeks.
GetAgeInWeeks = intAge
On Error Resume Next
MsgBox Err.Number & " " & Err.Description, vbCritical, "GetAgeInWeeks"
GetAgeInWeeks = 0
Download Code: basGetAgeInWeeks.zip