Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    100
    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
    7,902
    Thanks
    282
    Thanked 1,121 Times in 1,026 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

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,004
    Thanks
    90
    Thanked 459 Times in 419 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
    100
    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,004
    Thanks
    90
    Thanked 459 Times in 419 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
    100
    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,004
    Thanks
    90
    Thanked 459 Times in 419 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
  •