Results 1 to 6 of 6

VBA: Converting date from dd/mm/yyyy to yyyymmdd

This is a discussion on VBA: Converting date from dd/mm/yyyy to yyyymmdd within the Excel Questions forums, part of the Question Forums category; Hi, need your help. A report needs to name a sheet according the current business date. cob = 02/06/2008 ActiveSheet.Name ...

  1. #1
    New Member
    Join Date
    May 2005
    Location
    Tokyo
    Posts
    46

    Default VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Hi,

    need your help. A report needs to name a sheet according the current business date.

    cob = 02/06/2008
    ActiveSheet.Name = cob

    Due to the '/' the macro shows an error.
    As the cob is needed in this specific format for other parts in the macro, is there any command how the format can be changed to 20080602 for renaming the sheet?

    i.e.
    cob = 02/06/2008
    ActiveSheet.Name = Numberformat(yyyymmdd, cob)

    Any help is is very welcome.

    Thanks

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,316

    Default Re: VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Hi there,

    Not sure how you're assigning the "date" to the cob variable, but let me know if this is what you're after (or otherwise):

    Code:
    strYear = Year(cob)
    
        If Len(Month(cob)) = 1 Then
            strMonth = "0" & Month(cob)
        Else
            strMonth = Month(cob)
        End If
        
        If Len(Day(cob)) = 1 Then
            strDay = "0" & Day(cob)
        Else
            strDay = Day(cob)
        End If
        
    ActiveSheet.Name = strYear & strMonth & strDay
    HTH

    Robert

  3. #3
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,707

    Default Re: VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Hi,
    I have to assuming that cob is a string, since Excel will otherwise treat the formula you entered as 2 divided by 6 divided by 2008. With that assumption, the following code will accomplish what you need. The first option allows for entry of single-digit month and day; if you always have 2 digits for month and day, the second option is a much simpler solution:
    Option 1:
    Code:
    cob = "2/6/2008"
    cobYear = Right(cob, 4)
    cobDay = Format(Left(cob, InStr(1, cob, "/") - 1), "00")
    cobMonth = Format(Mid(cob, InStr(1, cob, "/") + 1, InStr(InStr(1, cob, "/") + 1, cob, "/") - InStr(1, cob, "/") - 1), "00")
    ActiveSheet.name = cobYear & cobMonth & cobDay
    Option 2, if you ALWAYS have 2 digits entered for each day and month:
    Code:
    cob = "02/06/2008"
    ActiveSheet.name = Right(cob, 4) & Mid(cob, 3, 2) & Left(cob, 2)
    Hope this helps,
    Cindy
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Sep 2007
    Posts
    602

    Default Re: VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Dates are, I believe, the worst nightmire in Excel and Access.
    I had so much trouble in the past with dates for applications to be use in several countries, that I renouced definitively to rely on built-in dates in Excel or (even more) in Access.
    Either I am now using the yyyymmdd format, or I ask for yyyy, mm and dd separately.

    Anyway, assuming your date will be correctly recognized, I would simply suggest using the following formula:

    yyyymmdd = 10000*year(cob) + 100*month(cob)+day(cob)

    (this is more readable than the serialDate ;>} )
    Last edited by lalbatros; Jun 2nd, 2008 at 01:09 AM.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Perhaps
    Code:
    ActiveSheet.Name = Format$(Date, "yyyymmdd")

  6. #6
    New Member
    Join Date
    May 2005
    Location
    Tokyo
    Posts
    46

    Default Re: VBA: Converting date from dd/mm/yyyy to yyyymmdd

    Thank you, all. That solved my problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com