Date formatting

[juslearning]

Hi,

At the moment I have a userform with several textboxes into which a date is inserted by clicking on a calendar. However I would like to only have to insert one date and then have code to format it several times so it can be used in the formula i have in the macro

Any pointers? thanks in advance

 

[AnotherHiggins]

I'm not clear on what exactly you are asking, but you can easily manipulate a captured date in VBA:

MyDateFormat1 = format(myDate, "yyyy-mm-dd")
MyDateFormat2 = format(myDate, "mm-dd-yy")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[juslearning]

HI, thanks for the response, sorry but am new to this whole programming thing, here is the code i have

Dim myvalue1 As String
Dim myvalue2 As String
Dim myvalue3 As String
Dim myvalue5 As String
Dim myvalue6 As String

myvalue1 = UserForm1.textbox3.Text = mydateformat2
mydateformat1 = Format("mmmyy")
myvalue2 = UserForm1.textbox3.Text
mydateformat2 = Format("mmmm yy") = myvalue2
myvalue5 = UserForm1.textbox3.Text
mydateformat3 = Format("mmm yy") = myvalue5
myvalue6 = UserForm1.textbox3.Text
mydateformat4 = Format("mmmyy") = myvalue6

Basically, if I can put this right, I want to capture one date via the textbox and then format it several times to fit into the formula i have in the macro. The date is in textbox3 and I want the output to be six dates in different formats.

 

[GlennUK]

You are attempting to use the Format function in the wrong way ... please look at anotherhiggins examples again.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ettienne]

Dim mydate as Date
Dim myvalue1 As String
Dim myvalue2 As String
Dim myvalue3 As String
Dim myvalue4 As String

mydate = DateValue(UserForm1.textbox3.Text)
myvalue1 = Format$(mydate,"mmmyy")
myvalue2 = Format$(mydate,"mmmm yy")
myvalue3 = Format$(mydate,"mmm yy")
myvalue4 = Format$(mydate,"mmmyy")

 

[juslearning]

wow, thanks guys it works like a treat, one more thing

with one of the values I need to subtract one month after the formatting has occurred.

 

[kodr]

Check out the following link, if you scroll down the page, there's a good write up that should get you started.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office08032000.asp

 

[juslearning]

thanks a bunch all who replied, got there in the end, saves me loads of time each week.

Just want to say what a cool site, hopefully will be more technical in my questions next time.

Thanks again.