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.

 

  1. 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.

 

(i)  date format change without using macro

 

(ii)dd/mm/yyyy

(iii)date

 

2) Using Macro 

Will generate result like this

 

output macro

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"