Change date format mm/dd/yyyy to dd/mm/yyyy in excel
This problem is faced by many Indian users as the U.S system of mm/dd/yyyy is quite strange to them and for them it creates problem each time they look at it. So here i am sharing some tips to fix it.
- Without using any code or Macro
First i am taking here an example of date 9/23/2015 now to convert it into dd/mm/yyyy use this.
Right click that particular cell in which date is given and choose format cells. Now find custom in left and paste dd”/”mm”/”yyyy in the space provided. Click ok and it will change.
(ii)
(iii)
2) Using Macro
Will generate result like this
Your ads will be inserted here by
Easy Plugin for AdSense.
Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.
Public Function ChangeDateFormat(inputString As String) As String
Dim firstDate As Date
Dim secondDate As Date
Dim trimmedInput As String
trimmedInput = Trim$(inputString)
firstDate = DateValue(Left$(trimmedInput, 10))
secondDate = DateValue(Right$(trimmedInput, 10))
ChangeDateFormat = Format(firstDate, "dd\/MM\/yyyy") & " - " & Format(secondDate, "dd\/MM\/yyyy")
End Function
Public Sub Test()
Sheet1.[B2] = ChangeDateFormat(Sheet1.[A2])
End Sub
3) Using formula .
A2 is cell name
=TEXT(DATE(RIGHT(LEFT(A2,10),4),LEFT(LEFT(A2,10),2),MID(LEFT(A2,10),4,2)),"dd/MM/yyyy") & " - " & TEXT(DATE(RIGHT(RIGHT(TRIM(A2),10),4),LEFT(RIGHT(TRIM(A2),10),2),MID(RIGHT(TRIM(A2),10),4,2)),"dd/MM/yyyy"