Change date format dd.mm.yyyy to dd/mm/yyyySolved/Closed

Question

Hello, 
I Want to change date format dd.mm.yyyy to dd/mm/yyyy in MS Excel 2003
Please solve my problem urgently
Shatrughna


System Configuration: Windows XP / Safari 536.11

AK · Accepted Answer

Format Cells---->Custom---->Enter dd"/"mm"/"yyyy in the space provided

10/11/2013 will be converted to 11/10/2013

venkat1926 · Answer

select the cell
click format-cells-custom
under "type" on the right side type
dd/mm/yy
and click ok

venkat1926 · Answer

that means the entry is NOT EXCEL DATE.  In some regions(mine also) it should be entered as m/d/yy and in some others as d/m/yy

suppose in my regions it is entered as in A1
15.11.2012 which you want to mean 15th November 2012

such entry will not be date but only a string
to convert in B1 (any empty cell) enter this formula 

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))+0

then result will be 41228 in B1 
if you format this as date (14-mar-01) the entry wil change to 
15-Nov-12
if your regional set up is different  modify the formula

venkat1926 · Answer

Pijush Sarkar -
what is not working. your comment is vague

venkat1926 · Answer

small mistake in mu formula 

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

venkat1926 · Answer

ignore my message

my old formula is correct.


when you type my formula what do you get

are your cells in format "text" change it to GENERAL

venkat1926 · Answer

Shartugns

see my first message above repeated here

that means the entry is NOT EXCEL DATE. In some regions(mine also) it should be entered as m/d/yy and in some others as d/m/yy 

suppose in my regions it is entered as in A1 
15.11.2012 which you want to mean 15th November 2012 

such entry will not be date but only a string 
to convert in B1 (any empty cell) enter this formula 

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))+0 

then result will be 41228 in B1 
if you format this as date (14-mar-01) the entry wil change to 
15-Nov-12 
if your regional set up is different modify the formula

venkat1926 · Answer

quote
I Want to change date format dd.mm.yyyy to dd/mm/yyyy in MS Excel 2003 
Please solve my problem urgently 
Shatrughna
unquote

as far as dates are concerned do not worry what you SEE in the cell. the correct entry is what you see in the formula bar.
it will be either d/m/yy or m/d/yy depending upon your regional configuration 

for seeing  your regional configuration
control panel-region and language

so you must ENTER date only in that format .  If you enter in some other way WRONG DATA is entered.

to format click format-dates-THERE ARE SOME STANDARD FORMATS
you can also use custom format and type whatever  format you want.

BUT REMEMBER WHAT EVER FORMAT YOU USED IT IS ONLY FOR VISUAL PURPOSE. FOR MANIPULATION THE ENTRY IS AS IN FORMULA BAR AND IT IS AN INTEGER

SEE HELP ON DATES IN EXCEL HELP

venkat1926 · Answer

if the date is in A1 use this formula in some other empty cell

=SUBSTITUTE(A1,".","/")

venkat1926 · Answer

Shatrughna
did you try custom foramt

venkat1926 · Answer

srini

what is meant by 4/08/14
in my compute it r is 8th april 14 and not 4th july

see your configuration of short date

see control panel+region and language

venkat1926 · Answer

sunny
my thread is 
quote
suppose in my regions it is entered as in A1 
15.11.2012 which you want to mean 15th November 2012 

such entry will not be date but only a string 
to convert in B1 (any empty cell) enter this formula 

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))+0 
unquote

you entered date asz 15.11.2012(15th november 2012) whereas you should have entered as 11/15/12

having entered as 15.11.12 which is a string at least in my computer
I have to convert to date as 15th november 2012

so I take first mid(a1,4,2)
take the fourth item and the lengthof 2
fourth item is 1 and length2 menas 11
then I add a slash "/"
and then add left two items that is 15 and the a slash 
and then last four items that is 2013.
so it will be 11/15/2012
but as you used mid left etc the final result that is 11/15/12 is also a string
something like '1234
to make it a date I have to add a zero to the whole text
then you get a date normally as a five figure number

dates are always taken as a number in excel
so you to format as date in any format 
dd-mmm-yyyyy that 15-nov-2012

darshan · Answer

thanks sir it worked..

venkat1926 · Answer

quote Hello, 
I Want to change date format dd.mm.yyyy to dd/mm/yyyy in MS Excel 2003 
Please solve my problem urgently 
Shatrughna
unquote

 this formula (provided the date is entered in your computer as dd/mm/yy, see control panel, region and language,short date)

=SUBSTITUTE(A1,".","/")

Change date format dd.mm.yyyy to dd/mm/yyyy

14 responses

Similar discussions

Subscribe To Our Newsletter!