vba format date

Re: vba format date

You could use vba to read the string and then use the mid function to remove the date from the end and save the first string back to the cell of a different cell. You could use a onchange object of the sheet for this to be done automatically

Re: vba format date

Set the variable 'As Date' and then assign the NOW function to the variable and it'll drop the time.

Re: vba format date

Why not just use Date()?

Re: vba format date

Maybe I missed something, but put

=NOW()

into any cell

right click on the cell, click on "format cells", "custom format" and enter yyyy-mm-dd

Does that do what you need?

Ger

Re: vba format date

rpfeil

What are you actually trying to do and where are you trying to do it?

You mention VBA in the topic title, are you actually using code?

Try this

MsgBox Format(Date, "yyyy-mm-dd")

Re: vba format date

In VBA as a variable simply do

Dim yourdate As String 
    
yourdate = Format(Date, "yyyy-mm-dd") 'Format Today() as specified

Re: vba format date

All the stuff on this thread present good and useful options, but just in case you run into a few issues I have, here's some more for you. Sometimes, excel/access behaves unpredictably. I have a db that generates dates using date() and other logic from a query, and then it exports the result set to a spreadsheet where additional macros fuse it with another sheet into a finished report. somewhere along the line, some dates became text while others became Date (type) and this has impact on how excel behaves. To get around this, I wrote a function that split the date() in 3 pieces like so:

year_string = year(date())
mo_string = month(date())
day_string = day(date())

then I also used logic on month and day so that if < 10 then day_string = "0" & day_string
then I put the pieces back together in a string using whatever punctuation I want and I get a perfectly formatted date of types:

01/01/2012
01-04-2012
or whatever ...

But if format() works for you, that might be simpler. Just posting this to provide options.
Note that all functions listed in this post are accessible from vba. object and are equally available in Access and Excel