Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Changing Date format from one to another

    Hi guys,

    What is the simplest way to change "July 6th, 2015" to "06/07/2015" using VBA?
    This is the cell that needs changing:
    Code:
    iBuyDate = shtStock.Cells(r, "H")
    shtSold.Cells(zDestRow, "K") = iBuyDate
    Thanks for looking,
    Ferenc
    Last edited by Ferenc Nagy; 2015-08-27 at 22:22.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    8,867
    Thanks
    336
    Thanked 1,355 Times in 1,233 Posts
    Ferenc,

    Code:
    With Sheets("Sheet1").Cells(1, "K")
        .Value = DateValue("July 6, 2015")
        .NumberFormat = "mm/dd/yyyy"
    End With
    Excel doesn't like the "th" thing! If your data has that you'll have to strip it.
    Code:
    strBegDate = "July 6th, 2015"
    strNewDate = Replace(strBegDate,"th,",",")
    strNewDate = Replace(strNewDate,"nd,",",")
    strNewDate = Replace(strNewDate,"rd,",",")
    strNewDate = Replace(strNewDate,"st,",",")
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,362
    Thanks
    101
    Thanked 570 Times in 519 Posts
    Ferenc,

    If RG's solution does not work, try changing your code line to:

    shtSold.Cells(zDestRow, "K") = #07/06/2015#

    then change the cell format to custom dd/mm/yyyy

    HTH,
    Maud
    Last edited by Maudibe; 2015-08-27 at 21:53.

  4. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Quote Originally Posted by Maudibe View Post
    Ferenc,

    If RG's solution does not work, try changing your code line to:

    shtSold.Cells(zDestRow, "K") = #06/07/2015#

    HTH,
    Maud
    From what I can tell, your example would change one specific and know date to a different format. If I know the date beforehand it would make no sense to bother writing the VBA to change it.
    The date can change, it is the format that is the same. For example date could be in the cell: "December 16th, 2015"

  5. #5
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,362
    Thanks
    101
    Thanked 570 Times in 519 Posts
    shtSold.Cells(zDestRow, "K") = "July 6th, 2015"
    Then your code line would make no sense either. Just following what you posted and what you asked.

    Maud

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Then your code line would make no sense either. Just following what you posted and what you asked.

    Maud
    I see what I did wrong... sometimes I forget not everybody can see what I am doing
    This is how the date gets into the cell... and the format is "Month Nth, Year" which I would like to change to "dd/mm/yyyy"

    Code:
    iBuyDate = shtStock.Cells(r, "H")
    shtSold.Cells(zDestRow, "K") = iBuyDate

  7. #7
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,362
    Thanks
    101
    Thanked 570 Times in 519 Posts
    Ferenc,

    Thanks for the explanation. Adapting RG's code to a function, here may be one solution:

    Code:
    Public Sub dsdf()
    iBuyDate = shtStock.Cells(r, "H")
    shtSold.Cells(zDestRow, "K") = CnvrtDate(iBuyDate)
    shtSold.Cells(zDestRow, "K").NumberFormat = "dd/mm/yyyy"
    End Sub
    
    
    Public Function CnvrtDate(str) As Date
    If InStr(1, str, "st", vbTextCompare) > 0 Then CnvrtDate = Replace(str, "st,", ","):: Exit Function
    If InStr(1, str, "nd", vbTextCompare) > 0 Then CnvrtDate = Replace(str, "nd,", ","):: Exit Function
    If InStr(1, str, "rd", vbTextCompare) > 0 Then CnvrtDate = Replace(str, "rd,", ","):: Exit Function
    If InStr(1, str, "th", vbTextCompare) > 0 Then CnvrtDate = Replace(str, "th,", ","):: Exit Function
    CnvrtDate = str
    End Function
    HTH,
    Maud
    Last edited by Maudibe; 2015-08-27 at 23:53.

Posting Permissions

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