How to change date format in excel from mm/dd/yyyy to dd/mm/yyyy

Date format changing is a basic requirement for daily operation. As this very helpful for daily report preparation and doing consolidate reports etc…

Let’s see how to do in an easy way. 

Step 1 :

First, we are going to change MM.DD.YYYY to DD-MM-YYYY

Date available in this format  

2.  we are going to change the date format first by use of MID,LEFT & RIGHT formula

 

RIGHT	MID	LEFT
=RIGHT(text,[num_chars])	MID =( text, start_num, num_chars)	=RIGHT(text,[num_chars])
Text : The text from which to extract characters on the right	Text :  The object or text to extract from	Text : The text from which to extract characters on the left
Num_chars: [ Optional] The number of characters to extract, Starting on the right side of text. Default is 1	Start_num : The location of the first character to extract	Num_chars: [ Optional] The number of characters to extract, Starting on the left side of text. Default is 1
	Num_chars : The number of characters to extract

Our data RIGHT is 05, MID is 07, LEFT is 2021, ie 05.07.2021

But we required 07-05-2021 so need to change the formula like this MID is 07, RIGHT is 05, LEFT is 2021.     

Use below formula in Cell F2      

  =MID(D2,4,2) &"-"& LEFT(D2,2) &"-"& RIGHT(D2,4)

Then date will change like this

 3.  Next, Changing Time Format

Time format, which is available for 12hrs, that we need to change to 24hrs.

Select range which you want to change -> Go to home -> Numbers -> Click More Number Formats , you get based on below screen shot

Then select customs -> Select h:mm -> Press OK

Now time change into 24hrs format

4. Now, combine Date & Time

We have date in this formula format so we need to change this into date format

For Changing date format, we need to copy respective date then Right click -> select paste values ( Choose numbers 123) then format change into date format like below

  Now we are going to combine G2 & E2 Cell

Above way you can combine date and time. If it’s helpful follow and subscribe me and comment below this information is helpful for you or not as well as if any doubts arise.