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

    ProjectFocus - not sure what you mean
    Robin1981 - as date did not remove the time

    norie - not sure how to use Date()\

    i have tried to pick out the date compontents but the month and day does not contian a place holder so when you (left(date,2).. It give you "7/".
    trying to get to yyyy-mm-dd

  • Re: vba format date

    Maybe I missed something, but put


    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?


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Re: vba format date


    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

    yes I am trying to code this in a vba script

    the closes I have gotten is this

    date1 = Format((Now - 1), "yyyy-mm-dd;@")

    output from this 7/21/2005

    but It still puts date1 is in the worng format, I need it in YYYY-MM-DD format to pass to a script that gets data through a OCBD connection.

  • 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:

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!