Change date format dd.mm.yyyy to dd/mm/yyyy [Solved/Closed]

Posts
1
Registration date
Wednesday July 18, 2012
Last seen
July 18, 2012
-
Hello,
I Want to change date format dd.mm.yyyy to dd/mm/yyyy in MS Excel 2003
Please solve my problem urgently
Shatrughna


See more 

14 replies

Best answer
approved by Israel Ayala on Feb 2, 2018
60
Thank you
Format Cells---->Custom---->Enter dd"/"mm"/"yyyy in the space provided

10/11/2013 will be converted to 11/10/2013

Say "Thank you" 60

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3046 users have said thank you to us this month

thanx alot..it worked
super.its working. thanks
Super.. it worked like charm
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
58
Thank you
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
Dear Venkat,

This formula is not working in the situation like 4/08/2014
Thank you so much venkat...
Thanks! ^^
1. A function DateTest() which takes a date value as string parameter in the "dd/mm/yyyy" format. (i.e. the value for December 4, 2014 will be passed as "04/12/2014") and test whether it is a valid date or not.If the date is valid then this function should return 1 and 0 otherwise. To check the validation test the following features:
Wonderful !!! you just saved my sleep for the day ! Salute bro
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
23
Thank you
select the cell
click format-cells-custom
under "type" on the right side type
dd/mm/yy
and click ok
thank u so much........its working.
Thanks sir it is working
thank you so much......... it's working
but if first number means dd is less than or equal to 12 then it's not working
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
14
Thank you
small mistake in mu formula

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
7
Thank you
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
7
Thank you
Shatrughna
did you try custom foramt
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
6
Thank you
if the date is in A1 use this formula in some other empty cell

=SUBSTITUTE(A1,".","/")
thnk u. you given easy way to change format
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
5
Thank you
Pijush Sarkar -
what is not working. your comment is vague
i have tried your way but didn't get such change
select the cells go to meny text to colomns > next > next > seelct date. change the format to MDY. Click ok.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
4
Thank you
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
4
Thank you
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,".","/")
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
3
Thank you
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
2
Thank you
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
Dear Venkat,

Thank for the solution. It's work for me. If you don't mind I want to understand this formula.

Qureshi
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
2
Thank you
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
2
Thank you
thanks sir it worked..