Autocomplete -Date

[John Keith]

If I enter the following date on today (Nov 30, 2009) 2/15 the date
that is entered is 2/15/2009. Is there anyway to get Excel to default
to entering 2/15/2010, in other words not complete the date with the
current calendar year but rather select the year of the next occurence
of the MM/DD?


John Keith
(e-mail address removed)

 

[Stefi]

You can either enter this formula in a separate cell:

=DATE(YEAR(A1)+(A1<TODAY()),MONTH(A1),DAY(A1))

or install this change event sub if you want the result in the input cell:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then _
Target = DateSerial(Year(Target) - (Target < Date), Month(Target),
Day(Target))
Application.EnableEvents = True
End Sub

Post if you need help to install it!
Regards,
Stefi


„John Keith†ezt írta:

 

[John Keith]

Stefi,

Thank you for the sugestions.

What would have been ideal for my situation would be a option
configuration switch but obviously that does not exist :-(

You can either enter this formula in a separate cell:

=DATE(YEAR(A1)+(A1<TODAY()),MONTH(A1),DAY(A1))

or install this change event sub if you want the result in the input cell:

John Keith
(e-mail address removed)

 

[Stefi]

You are welcome! Thanks for the feedback!
This is really a specific job, you cannot expect a solution ready to use.
Clicking the YES button will be appreciated.


--
Regards!
Stefi



„John Keith†ezt írta: