How can I calculate the difference, in # of weeks, between two dates that span a new year
Posted on 2012-09-06
How can I calculate the difference, in # of weeks, between two dates that span a new year? I have code below that assigns the the week number to a variable for datefields using the format function then distributes the number of hours entered in another field across each of the weeks between the two dates. ProjToTest is the begin /first date, ProjWitnessTest is the end / second date.
I need to be able to distribute the hours across the weeks when the dates span a new year. The year is stored as a seperate field (i.e. 2012) also.
Dim cwProjToTest As Integer
Dim cwProjWitnessTest As Integer
If IsDate(Me.ProjToTest) = True Then cwProjToTest = Format(Me.ProjToTest, "ww", vbMonday, vbFirstFourDays)
If IsDate(Me.ProjWitnessTest) = True Then cwProjWitnessTest = Format(Me.ProjWitnessTest, "ww", vbMonday, vbFirstFourDays)
weeks = cwProjWitnessTest - cwProjToTest + 1
i = 0
If cwProjToTest >= 1 Then
MsgBox "cwProjToTest >1", vbOKOnly
Do Until i = weeks
tblWork!Engineer = Me.Engineer
tblWork.Week = cwProjToTest - i
If IsDate(Me.ProjToTest) Then tblWork.Year = Format(Me.ProjToTest, "yyyy")
tblWork.Hours = Round(Me.TestHours / weeks, 0)
tblWork.Reason = Me.Order
tblWork.Note = "Test"
i = i + 1