Consulting

Results 1 to 6 of 6

Thread: Convert date to string yyyymmdd

  1. #1
    VBAX Regular
    Joined
    May 2013
    Posts
    14
    Location

    Convert date to string yyyymmdd

    Hello everyone: it has been years since i have touch vba and it appears what i knew back in the day no longer works in todays world lol... anyway here is what i am trying to do. I create new tabs everyday in an excel spreadsheet and name it ex. 20130531. i am trying to create a string that will allow me to call this page so i can gather data from it. i keep getting the value 5/31/2013 so it is not locating the tab. i have tried converting the date in the spreadsheet itself however it keeps giving me the wrong value as well.

    Can anyone help?

    Thanks
    Tom

  2. #2
    VBAX Regular
    Joined
    May 2013
    Posts
    14
    Location
    here are some of things i have tried

    Dim dtmdate As Date
    Dim strSheets As String
    strSheets = Format(Now, "yyyymmdd")
    dtmdate = Now() '.NumberFormat = "yyyymmdd"
    Range("A1").Select
    'Selection.NumberFormat = "yyyymmdd": ActiveCell.FormulaR1C1 = "=NOW()" ': 
    Selection.NumberFormat = "yyyymmdd"
    'strSheets = Range("A1")
    'dtmdate.numberformet = "yyyymmdd"
    'Selection.NumberFormat = "yyyymmdd"
    ActiveCell = dtmdate
    strSheets = Range("A1")
        Sheets("strSheets").Select
    Last edited by Aussiebear; 03-29-2023 at 05:56 PM. Reason: Adjusted code tags

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    We just discussed this recently.

    strSheets = Format(Date, "yyyymmdd")
    Last edited by Aussiebear; 04-06-2023 at 09:50 PM. Reason: Adjusted the code tags

  4. #4
    VBAX Regular
    Joined
    May 2013
    Posts
    14
    Location
    Kenneth thanks I get a compile error when I use Date just like when I use the Format. I must be missing some libraries in my VBA even though I can see them in my object browser.

    Thanks
    Tom

  5. #5
    VBAX Regular
    Joined
    May 2013
    Posts
    14
    Location
    My issue is fixed Kenneth your suggestion worked.

    Thanks
    Tom

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Some VBA alternatives:

     
    Sub M_snb()
        MsgBox DatePart("yyyy", Date) & Format(DatePart("m", Date), "00") & Format(DatePart("d", Date), "00")
        MsgBox [text(today(),"yyyymmdd")]
        MsgBox Application.Text(Date, "yyyymmdd")
    End Sub
    Last edited by Aussiebear; 03-29-2023 at 05:57 PM. Reason: Adjusted the code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •