Add exactly 1 Year in days to Textbox date

  • Thread starter Thread starter Corey ....
  • Start date Start date
C

Corey ....

How can i ADD 1 year to Textbox32's date entered and have it placed into
Textbox33 ?

Private Sub TextBox32_AfterUpdate()
TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <=== Gives
me an error
End Sub

Corey....
 
If you already know the exact format the user will use to enter the date -
"2009-02-23" or "Feb 23, 2009" or whatever - then you can take the text value
of Textbox32, parse out the year, add 1 to it, insert it back into the date
and put it in Textbox33.

But personally I prefer to let people use their own date formats without
making my program impose a standard. To do that, use
DATEVALUE(Textbox32.Text) to produce a date value. Depending on what "adding
one year" means to you, you can simply add 365 to that value to get a date
that is 365 days later, or you can use the DateAdd function to add a year to
it, or whatever. Then place - or you can Sounds to me like you need to
convert Textbox32's text value to a date, using the DATEVALUE function. You
can then use the Format function to turn the date back into a string and put
that in Textbox33.
 
Thanks for the helpful info Bob.

Was still getting an error there for a bit, but had the textbox formatted
BEFORE i used the DateValue().

All is good now thatks.

Corey....
 
Here is one way (although I'm not sure what event procedure you will want to
put it in)...

Dim D As Date
If IsDate(TextBox32.Text) Then
D = CDate(TextBox32.Text)
TextBox33.Text = D + 365 - (Day(D) <> Day(D + 365))
End If
 
Be careful if you extract the year and add 1 to it. You wouldn't want Feb
29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
changing the year.
Mike F
 
Be careful if you extract the year and add 1 to it. You wouldn't want Feb
29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
changing the year.
Click to expand...

How, exactly, did you get a result of Feb 29, 2009?

Although you may get unexpected answers adding 1 year to the 29-feb-2008,
(i.e., depending on your method, either 28-feb-2009 or 1-mar-2009), I don't see
how you obtained a result of 29-feb-2009.

To the best of my knowledge, neither the Date data type, nor a worksheet cell
formatted as Date, will support a date of 29-feb-2009.

I suppose you could construct a text string, but that would not be recognized
or used as a date by either VBA or Excel.
--ron
 
Did you mean your message to be a response to my posting? If so, that is not
what my code does. If you are not sure, try it out... it will not produce
Feb 29th in any year.
 
No Rick, I endorse your code, just warning about creating a False date with
a string, like Ron mentioned could happen but would not really be a
"recognized" date by Excel.
 
Back
Top