Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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. --- "Corey ...." wrote: How can i ADD 1 year to Textbox32's date entered and have it placed into Textbox33 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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.... "Bob Bridges" wrote in message ... 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. --- "Corey ...." wrote: How can i ADD 1 year to Textbox32's date entered and have it placed into Textbox33 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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 -- Rick (MVP - Excel) "Corey ...." wrote in message ... 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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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 "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Corey ...." wrote in message ... 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.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
On Tue, 24 Feb 2009 06:48:56 -0500, "Mike Fogleman"
wrote: 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. 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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. -- Rick (MVP - Excel) "Mike Fogleman" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Corey ...." wrote in message ... 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.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add exactly 1 Year in days to Textbox date
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. "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Mike Fogleman" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Corey ...." wrote in message ... 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Figure out date from total days in year | Excel Worksheet Functions | |||
Date formula resulting in Year, Months & days | Excel Worksheet Functions | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Date to Days of the year | Excel Worksheet Functions | |||
i have two days and i want the difference in days, months, year | Excel Worksheet Functions |