Report

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

Ask a question shatrughna 1Posts Wednesday July 18, 2012Registration date July 18, 2012 Last seen - Last answered on Jul 3, 2017 at 01:22 PM by Ravi
Hello,
I Want to change date format dd.mm.yyyy to dd/mm/yyyy in MS Excel 2003
Please solve my problem urgently
Shatrughna


Helpful
+60
plus moins
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?  
Srini- Sep 10, 2014 at 04:20 AM
Dear Venkat,

This formula is not working in the situation like 4/08/2014
Muthuraj- Sep 11, 2014 at 11:55 PM
Thank you so much venkat...
Deepak- Dec 3, 2014 at 01:11 AM
Thanks! ^^
qurat- Dec 16, 2014 at 05:27 AM
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:
Ravi- Jul 3, 2017 at 01:22 PM
Wonderful !!! you just saved my sleep for the day ! Salute bro
Reply
Helpful
+36
plus moins
Format Cells---->Custom---->Enter dd"/"mm"/"yyyy in the space provided

10/11/2013 will be converted to 11/10/2013
roibba- Feb 5, 2014 at 10:22 PM
this works for me. thanks
anju- Aug 2, 2014 at 05:04 AM
thanx alot..it worked
MMj- Aug 26, 2014 at 05:51 AM
super.its working. thanks
Siva Cheruvu- Nov 13, 2014 at 07:58 PM
Super.. it worked like charm
Helpful
+23
plus moins
select the cell
click format-cells-custom
under "type" on the right side type
dd/mm/yy
and click ok
Avi- Oct 19, 2013 at 07:05 AM
not working
payal- Dec 14, 2013 at 07:14 AM
thank u so much........its working.
Varesh- Jan 18, 2014 at 05:35 AM
Thanks sir it is working
Kiran- Feb 13, 2014 at 11:55 PM
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
Helpful
+14
plus moins
small mistake in mu formula

=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))
Helpful
+7
plus moins
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
Helpful
+7
plus moins
Shatrughna
did you try custom foramt
Helpful
+6
plus moins
if the date is in A1 use this formula in some other empty cell

=SUBSTITUTE(A1,".","/")
prt- Jun 11, 2014 at 05:23 AM
thnk u. you given easy way to change format
Helpful
+5
plus moins
Pijush Sarkar -
what is not working. your comment is vague
Avi- Oct 19, 2013 at 07:06 AM
i have tried your way but didn't get such change
Excel- Jan 14, 2014 at 12:19 PM
select the cells go to meny text to colomns > next > next > seelct date. change the format to MDY. Click ok.
Helpful
+4
plus moins
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
Helpful
+4
plus moins
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,".","/")
Helpful
+3
plus moins
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
Helpful
+2
plus moins
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
Sunny- Oct 14, 2014 at 02:37 AM
Dear Venkat,

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

Qureshi
Helpful
+2
plus moins
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
Helpful
+2
plus moins
thanks sir it worked..

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!