-2

I would like to know how to convert date "17/03/10" to "10/03/17".

17 is the year, 03 is the month and 10 is the day. Can I get a VBA code for this to use in Excel?

Community
  • 1
  • 1
rony921
  • 3
  • 3
  • Show us what you tried – Mawcel Mar 11 '17 at 12:35
  • Do you need code, or do you just need to format the cell? Where is the date that you want to convert, and where do you want the converted value to show? – Skippy Mar 11 '17 at 12:43
  • formatting is not working because excel is taking 10 as 2010 . – rony921 Mar 11 '17 at 13:09
  • I'm voting to close this as a duplicate because the YMD format almost assuredly came from a text import and should have been corrected during a proper import process, not repaired after-the-fact because a CSV was double-clicked from an File Explorer window. –  Mar 11 '17 at 18:28
  • btw, are there any 'pseudo-dates' with years earlier than 2013? Are you on a MDY regional setting (typical North American)? –  Mar 11 '17 at 18:33

2 Answers2

1

I've assumed because of your use of quotes that these are strings representing dates you want to convert, and not date values.

Therefore, try this:

strOldDate = "17/03/10"
strNewDate = Mid$(strOldDate, 7, 2) & "/" & _
             Mid$(strOldDate, 4, 2) & "/" & _
             Mid$(strOldDate, 1, 2)
MsgBox strNewDate  'Outputs "10/03/17"
M1chael
  • 251
  • 1
  • 9
  • hi M1chael i want to select range and change the format. sorry for the misunderstanding.just as an example i showed. like this when i get date format in 17/03/10,i need to get as real date 10/03/17.if there are different dates how to apply? please help – rony921 Mar 11 '17 at 13:46
0

Try the below, I've placed in into a function so you should be able to more easily include it within your code (And even use it in formula)

'// The actual function
Function ReformatDate(WeirdDate As String) As Date
    ReformatDate = Right(WeirdDate, 2) _
        & Mid(WeirdDate, 3, 4) _
        & Left(WeirdDate, 2)
End Function

'// Testing with whatever is selected
Sub TestFixSelection()
    For Each Cell In Selection
        Cell.Value = ReformatDate(Cell.Value)
    Next
End Sub

'// Testing over a fixed range
Sub TestFixFixedRange()
    For Each Cell In [A1:A10]
        Cell.Value = ReformatDate(Cell.Value)
    Next
End Sub

Be aware this corrects the string and turns it into a date, not a string representation of a date. Don't be afraid of Excel Dates, they're your friends.

Richard
  • 312
  • 1
  • 6