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

shatrughna 1 Posts Wednesday July 18, 2012Registration date July 18, 2012 Last seen - Jul 18, 2012 at 01:49 AM - Latest reply:  Ravi
- Jul 3, 2017 at 01:22 PM
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 

46 replies

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Nov 14, 2012 at 10:09 PM
+62
Helpful
16
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
Was this answer helpful?  
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
+37
Helpful
5
Format Cells---->Custom---->Enter dd"/"mm"/"yyyy in the space provided

10/11/2013 will be converted to 11/10/2013
this works for me. thanks
thanx alot..it worked
super.its working. thanks
Super.. it worked like charm
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Jul 19, 2012 at 11:48 PM
+23
Helpful
7
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
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Jan 14, 2014 at 09:33 PM
+14
Helpful
small mistake in mu formula

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Mar 21, 2014 at 05:09 AM
+7
Helpful
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Jul 31, 2014 at 01:18 AM
+7
Helpful
Shatrughna
did you try custom foramt
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Mar 27, 2014 at 03:36 AM
+6
Helpful
1
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
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Jul 19, 2013 at 09:53 AM
+5
Helpful
2
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.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Sep 10, 2014 at 08:30 AM
+4
Helpful
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Dec 16, 2014 at 06:25 AM
+4
Helpful
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,".","/")
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Jan 14, 2014 at 09:45 PM
+3
Helpful
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Feb 14, 2014 at 12:10 AM
+2
Helpful
1
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
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus March 10, 2016 Last seen - Oct 14, 2014 at 07:29 AM
+2
Helpful
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
Helpful
thanks sir it worked..