Leap year date calculation

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub
 
Rachel said:
I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub
Click to expand...

Use the DateAdd() function. It takes leap years into account.

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DateAdd("yyyy", 1, [wc eff])
End If
End Sub
 
I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub
Click to expand...

add 1 to the year like:

[wc exp/can] =
dateserial(year([wc eff]) + 1,month([wc eff]),day([wc eff])

if month([wc eff])=2 AND day([wc eff])=29 then
[wc exp/can] = [wc exp/can] -1
' the 29th of a nonLeapYear would be the 1 of march
endif

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
What about:

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DATEADD("yyyy", 1, [wc eff])
End If
End Sub

I think this will alleviate your problem...
 
It worked!!! You guys are awesome. This was driving me
nuts.. Thank you very much
Rachel
-----Original Message-----
What about:

Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = DATEADD("yyyy", 1, [wc eff])
End If
End Sub

I think this will alleviate your problem...

--
James Goodman



I have a form we use for tracking annual policy
information. We enter the effective date and it
calculates the expiration date by adding 365 days.
Because of this being a leap year, any policy term that
crosses over 2/29/04 has an expiration date that is 1 day
short. This is causing all kinds of problems. Does
anyone have a solution? Here is the current code:
Private Sub WC_Exp_Can_Enter()
If [wc eff] > 0 Then
[wc exp/can] = [wc eff] + 365
End If
End Sub
Click to expand...


.
Click to expand...
 
Back
Top